Skip to main content

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 License and RTK
    To deploy to the Azure cloud, you will need a license (full or trial) and a Runtime Key (RTK) for each of the CData components being deployed. For more information on obtaining this license (or a trial), 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
    With PowerShellGet you may install using Install-Module -Name AzureRM -AllowClobber, or by using a .msi installer from Microsoft Releases. You can find other detailed instructions on obtaining the supported Azure PowerShell from Microsoft's Web Documentation.
  • 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.
    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.

    ssis-azure-datafactory-deploy-1.png

  2. Allow IP addresses on the SQL Server Firewall as necessary.

    ssis-azure-datafactory-deploy-2.png

  3. Run the AzureDeploy.ps1 PowerShell script, and provide the required parameters.
    The default location (example Salesforce 2018) is C:\Program Files\CData\CData SSIS Components for Salesforce 2018\lib
    You may use powershell –ExecutionPolicy Bypass to bypass execution policy restrictions for the current session.

    ssis-azure-datafactory-deploy-3.png


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

    ssis-azure-datafactory-deploy-12.png

  4. Log into Azure in the generated pop-up.

    ssis-azure-datafactory-deploy-4.png

  5. After logging into Azure, the script creates the resources necessary for deployment and starts the Integration Runtime.

    ssis-azure-datafactory-deploy-5.png

  6. You are now ready to deploy your SSIS Package.
    In Visual Studio, right-click the project -> Deploy.

    ssis-azure-datafactory-deploy-6.png


    Use the Deployment Wizard to select your project for deployment and authenticate to the Azure Server as your destination.

    ssis-azure-datafactory-deploy-7.png


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

    ssis-azure-datafactory-deploy-10.png

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
    In Options -> Connection Properties, set the 'Connect to Database' field to SSISDB.

    ssis-azure-datafactory-deploy-8.png

  2. Configure the SSIS project in the Integration Services Catalog (right-click the project -> Configure).
    (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.

    ssis-azure-datafactory-deploy-11.png

  3. 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.

I followed the instructions and everything seems to be set up correctly. However, when I execute the SSIS package via the data factory pipeline, I am receiving a message that there are errors during validation because the classes from the CData components are not registered. 

 

The output from the install log states that the DEPLOY succeeded.

Copying files to "C:\Program Files\Microsoft SQL Server\140\DTS\Connections\"
C:CData.SSIS2017.D365BusinessCentral.dll
1 File(s) copied
Copying files to "C:\Program Files\Microsoft SQL Server\140\DTS\PipelineComponents\"
C:CData.SSIS2017.D365BusinessCentral.dll
1 File(s) copied
Copying files to "C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Connections\"
C:CData.SSIS2017.D365BusinessCentral.dll
1 File(s) copied
Copying files to "C:\Program Files (x86)\Microsoft SQL Server\140\DTS\PipelineComponents\"
C:CData.SSIS2017.D365BusinessCentral.dll
1 File(s) copied
DEPLOY succeeded.

I have recreated the Integration Runtime multiple times and it always succeeds and starts only to have the component fail during validation. The project runs perfect from Visual Studio. 

Is there something else I can do to debug this issue? I am not sure what else to do

 

Thanks,

--Steve 
 


Reply