This post has been republished via RSS; it originally appeared at: SQL Server Integration Services (SSIS) articles.
The feature of Azure-enabled SQL Server Integration Services (SSIS) projects on SQL Server Data Tools (SSDT) allows you to run packages on Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF).
You can download below SSDT versions to use the feature:
Sample SSIS package is to transfer data from on-premises file (E:\Input.txt) to Azure SQL.
You can perform below operations to run that package in Azure:
Step 1: Azure-enabling your SSIS project.
Please note, it requires you to set target server version of SSIS project to be the latest one supported by Azure-SSIS IR, which is currently SQL Server 2017.
Step 2: Connect your project to SSIS Platform-as-a-Service (PaaS) in Azure Data Factory.
On the Select SSIS IR in ADF page, select your existing ADF and Azure-SSIS IR to run packages or create new ones if you do not have any.
If your existing Azure subscription does not have any Azure Data Factory, you can click on the Create SSIS IR button to launch the Integration Runtime Creation Wizard, where you can enter the location and prefix for us to automatically create a new Azure Resource Group, Data Factory, and SSIS IR on your behalf.
On the Select Azure Storage page, select your existing Azure Storage account to upload packages into Azure Files or create a new one if you do not have any.
Step 3: Run your package in Azure
If your Azure-SSIS Integration Runtime is not started, a dialog window will pop up to start it.
You will be able to see the logs of execution on the “Progress” tab same as local run and log content will be refreshed every minute until execution completes.
Step 4: Adjust your package to solve compatibility issue if there is
You might see package execution failed as above if there is some compatibility issue to lift & shift your package from on-prem to cloud. For this sample package, as it refers to a on-premises file which Azure-SSIS Integration Runtime can’t access directly, package execution will fail.
You can have 3 options to solve the problem:
- Leverage Azure File share to store the file and configure Azure-SSIS IR to access that Azure File share via cmdkey with custom setup
- Put file into on-premises file share and join Azure-SSIS IR into VNet to access on-premises file share
- Configure Self-Hosted IR as proxy to access on-premises file
Take option 1 as example, upload file to \\ssisdemotest.file.core.windows.net\share\Input.txt and then update file connection manager in package to refer to new file location.
Then configure your Azure-SSIS IR with custom setup script:
cmdkey /add:ssisdemotest.file.core.windows.net /user:azure\ssisdemotest /pass: [storage_key]
Try to execute package in Azure again and execution will pass.
For more information about Azure-Enabled feature, please refer to
https://docs.microsoft.com/en-us/azure/data-factory/how-to-invoke-ssis-package-ssdt