Deploy CData SSIS Components on Azure Data Factory



Azure Data Factory is a scalable, trusted, cloud-based solution for building automated data integration solutions with a visual, drag-and-drop UI. Moving on-premises SSIS workloads to Azure can reduce the operational costs of managing infrastructure, increase availability with the ability to specify multiple nodes per cluster and deliver rapid scalability. 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.

In this article, we walk through the steps for deploying CData SSIS Components to Azure.

Deployment Prerequisites



The deployment of CData Components requires the following:

  • CData SSIS Component Server License
  • To deploy to the cloud, you need a Server license and a Runtime Key (RTK) for the CData products being deployed. For more information on obtaining this license, contact our sales team.
  • Azure Subscription
  • This can be managed in the Azure Portal. More information on Azure Subscriptions is available from the Microsoft website.
  • AzureRM PowerShell 6.2.0 or higher
  • You can find instructions on obtaining the supported Azure PowerShell from the Microsoft Documentation, or you can use the .msi installer (direct download). After installing the new version, you may need to run the cmdlet: Set-ExecutionPolicy Unrestricted
  • SSMS 2012 or higher
  • We recommend using the latest version available from Microsoft.

Deployment Guide



Overview

  • Create an Azure Resource Group
  • Create an Azure SQL Server (logical server)
  • Run CData PowerShell Script AzureDeploy.ps1 to set up and create an Integration Runtime
  • Deploy SSIS Project From Visual Studio to Azure
  • Use SSMS to manage and execute a deployed project

Step-by-Step Guide

  1. Create an Azure SQL Server (logical server) and Resource Group or reuse an existing one.
  2. If the existing SQL Server already has an Integration Runtime (and SSISDB), then you may overwrite it by specifying its name with the -InterationRuntimeName parameter. Each SQL Server may only have one Integration Runtime.
  3. Allow IP addresses on the SQL Server Firewall as necessary.
  4. Run the AzureDeploy.ps1 PowerShell script, and provide the required parameters.
  5. The default location (example Salesforce 2018) is C:\Program Files\CData\CData SSIS Components for Salesforce 2018\lib

    (Optional) Deploying Multiple CData Components
    To deploy multiple CData components, copy all additional 2017 CData .dll and .Design.dll files to the current directory.

  6. Log into Azure in the generated pop-up.
  7. After logging into Azure, the script creates the resources necessary for deployment and starts the Integration Runtime.
  8. You are now ready to deploy your SSIS Package.
  9. In Visual Studio, right-click the project -> Deploy. Use the Deployment Wizard to select your project for deployment and authenticate to the Azure Server as your destination.
    After this step, your package is deployed and accessible in the Azure Data Factory web UI at: https://adf.azure.com

Managing the Project in SSMS

By default, connection settings with sensitive information (passwords, security tokens, etc.) are redacted when deploying to Azure. You may provide this information again through SSMS.

  1. Connect to the Azure SQL Server
  2. In Options -> Connection Properties, set the 'Connect to Database' field to SSISDB.
  3. Configure the SSIS project in the Integration Services Catalog (right-click the project -> Configure).
  4. (Note: If you do not see Integration Services Catalogs, you may need to upgrade your SSMS version or set 'SSISDB' in step 1.)
    • Add connection information as necessary in the Connection Manager tab. This is where you can add your RTK.
  5. Execute the project (right-click the project -> Execute).

From here, the project is executed in the configured Azure Data Factory, and the results of executions can be viewed by right-clicking the project -> Reports -> All Executions.