With the release of Azure Data Factory V2 integration runtimes (ADFv2 IR), deployment to the Azure cloud is now possible for SSIS projects that use CData components. Follow the steps below to deploy the components to Azure. You can then execute SSIS projects in the configured Azure Data Factory.

Complete the following tasks:

• Run the AzureDeploy.ps1 script to provision an integration runtime.
• Deploy the SSIS project From Visual Studio to Azure.
• Use SSMS to manage and execute a deployed project.

Prerequisites

In addition to an Azure subscription, you need the following to deploy the components:

• SSMS 2014 or higher
• Azure resource group

• Azure SQL database

  Confirm that the SQL Server's firewall settings allows access from the client machine: From the SQL Server's overview page, click Set Server Firewall and add the IP address of the client machine, or a range of IP addresses that includes the IP address of the client machine. Additionally, ensure that the Allow Azure services and resources to access this server option is enabled in the firewall settings.

• CData SSIS component server license

  To deploy CData SSIS components to the cloud, you need a server license and a runtime key (RTK).

• Azure Az PowerShell module 6.0.0 or higher

  PowerShell 7.0.6 LTS, PowerShell 7.1.3, or higher is recommended. If you are using an older version:

  1. Install a new PowerShell.
  2. Run Uninstall-AzureRM in the new PowerShell.
  3. Install AZ module in the new PowerShell.
  You can install the Az PowerShell Module by using the Install-Module cmdlet or downloading the .msi installer. After installing, you may need to run the following:
  

  Set-ExecutionPolicy Unrestricted

  
  

  Connect-AzAccount

Deploying CData SSIS Components to Azure

Follow the procedure below to deploy SSIS projects using one or more CData data sources:

Provision an Integration Runtime

Use the included AzureDeploy.ps1 script to provision and start an Azure SSIS integration runtime.

Note: You need to enable Settings > Configuration > Allow Blob anonymous access in your storage account before you can deploy.

1. To deploy multiple CData SSIS components, copy all other CData SSIS2017.dll and .Design.dll files to this component's /lib folder. By default, this is C:\Program Files\CData\CData SSIS Components for Microsoft Excel\lib.

2. Run the AzureDeploy.ps1 script from this /lib directory. You may specify all parameters required parameters at once. For example:
   

   .\AzureDeploy.ps1 -ResourceGroupName "my-resource-group" -SubscriptionId "2d91834e-1hga-4c31-86yf-dba7b40b90u2" -SqlServerName "my-sql-db.database.windows.net" -SqlDatabaseUser "MySQLUser" -SqlDatabasePwd "MySQLPwd" -DataFactoryName "MyDataFactory" -StorageAccountName "MyStorageAccount"

   If your SQL Server database already has an integration runtime (and SSISDB), you can overwrite it by first stopping it, and then specifying its name with the -InterationRuntimeName parameter. Each SQL Server may only have one integration runtime.

   See the "Configuring the AzureDeploy.ps1 Script" section below for more information on the available parameters.

3. Log into Azure in the dialog that is displayed.

   After you log into Azure, the script creates the resources necessary for deployment and starts the integration runtime.

Deploy the SSIS Package

You are now ready to deploy your SSIS package:

1. In Visual Studio, right-click the project and select Deploy. The Integration Services Deployment Wizard is displayed.
2. On the Select Source page, select your SSIS project. You can select a project deployment file or a project that resides in an SSIS catalog.

3. On the Select Destination page, enter the fully qualified domain name of the logical SQL database and enter your authentication information. Select Browse to select the target folder in SSISDB.

   After this step, your package is deployed and accessible in the Azure Data Factory web UI at https://adf.azure.com.

Managing and Running the Project in SSMS

By default, connection settings with sensitive information (passwords, security tokens, etc.) are redacted when deploying to Azure. To be able to execute projects in the configured Azure Data Factory, provide this information through SSMS.

1. Connect to the Azure SQL database. In Options > Connection Properties, set the Connect to Database field to "SSISDB".

2. Right-click the project and click Configure to configure the SSIS project in the Integration Services Catalog.

   Note: If you do not see Integration Services Catalogs, you may need to upgrade your SSMS version or set "SSISDB" in step 1.

3. Add connection information as necessary in the Connection Manager tab. Provide the RTK connection property.

You can then execute the project. View the results of execution by right-clicking the project and clicking Reports -> All Executions.

Configuring the AzureDeploy.ps1 Script

The following sections provide a reference to the available options for the deployment script:

Required Parameters

• ResourceGroupName: The name of the resource group to use or create resources in. The resource group must exist.
• SqlServerName: The name or endpoint of the logical SQL database. Example: ssishost.database.windows.net.
• SqlDatabaseUser: The username of the SQL Server user.
• SqlDatabasePwd: The password for the SQL Server user.
• DataFactoryName: The name of the Data Factory to use (or create). A data factory may only have one integration runtime. To overwrite an existing IR, specify its name with the -IntegrationRuntimeName parameter.
• StorageAccountName: The name of the storage account to use (or create).

Other Parameters

• SubscriptionId: The Id of the subscription to use for creating additional resources. This should match a subscription from ResourceGroupName. Example: 2r29814e-1dba-4b11-81cf-dba7b90b74c3
• Location: The location to create additional resources. Defaults to "EastUS".
• SetupContainerName: The name of the blob container to create (or reuse). CData assembly files, main.cmd, and SSISDeployUtil.bat will be overwritten if they exist. Defaults to "ssissetup".
• StorageAccountResourceGroup: If the storage account to be used is on a different resource group, this parameter specifies the name of the resource group for the -StorageAccountName to use. Defaults to -ResourceGroup otherwise.
• RuntimeNodeSize: The integration runtime node size. Defaults to "Standard_D1_v2".
• RuntimeNodeCount: The number of target nodes of the integration runtime. Defaults to 1.
• AzureSSISMaxParallelExecutionsPerNode: Max parallel executions per node. Defaults to 1.
• CatalogPricingTier: The catalog database pricing tier of the integration runtime. Defaults to "Basic".
• AzureSSISEdition: The edition of the SSIS integration runtime. Standard or Enterprise. Defaults to Standard.
• AzureSSISDescription: A description you may provide for the Azure SSIS Runtime. Defaults to "Azure SSIS Runtime".
• IntegrationRuntimeName: The name of the integration runtime to create (or overwrite). Defaults to "AzureSSISIR".

  Each SQL Server can have only one integration runtime. If the existing SQL Server already has an integration runtime and SSISDB (the SSIS catalog database), then you may overwrite it by specifying the -InterationRuntimeName parameter.

Using the AzureLogfile.ps1 Script

The AzureLogfile script assists with mounting an Azure Files file share to both your local system and to your provisioned SSIS IR. As the name suggests, this is useful for troubleshooting with a CData Logfile, but it can also be useful for specifying schema files with the Location property, or for reading/writing to flat files from a deployed package.

Required Parameters

• ResourceGroupName: The name of the resource group to use or create resources in. The resource group must exist.
• StorageAccountName: The name of the storage account to use.
• FileShareName: The name of the file share to use (or create) for mounting.
• DriveLetter: The single-character drive letter to mount the file share to on the local system
• SqlServerName: The name or endpoint of the logical SQL database. Example: ssishost.database.windows.net.
• SqlDatabaseUser: The username of the SQL Server user.
• SqlDatabasePwd: The password for the SQL Server user.