Run SSIS Package in Azure via SSDT

[Jarod_Zhu]

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:

• VS 2017 SSDT version 15.9.2
• VS 2019 Integration Service extension, version 3.1

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:

1. Leverage Azure File share to store the file and configure Azure-SSIS IR to access that Azure File share via cmdkey with custom setup
2. Put file into on-premises file share and join Azure-SSIS IR into VNet to access on-premises file share
3. 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

[DennisD]

Amazing feature! Tried today myself, but i keep getting a error:

VS is a new install :)

[Jarod_Zhu]

This turn out to be a bug in product that package with container will fail when execute in Azure and it will be fixed in next release. Thanks for reporting.

[obozdemir]

We use SQL Server 2012, SSIS 2012 and Visual Studio 2012.

Our target is to send data to the Azure system. For this, we did the steps on the following website, Azure Feature Pack Setup was installed, Java was installed, TLS 1.2 was set.

https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-service...

I developed and tested the ETL package in Visual Studio on my local computer. I have tested it many times.
I used "Flexible File Destination" for data submission. Source: Oracle Database, Destination: Azure- "Flexible File Destination".
I requested an installation on the SSIS server. Installations have been made. Azure Feature Pack was installed on the SSIS server, Java was installed, TLS 1.2 setting was made. There is no incomplete installation. But after deployed, I run the package from SSIS catalog, it waits for a long time and gives an error.

In summary, we do not do the deploy process to the azure system. We do the deploy operation to the usual SQL Server 2012/SSIS 2012.

Sent To Data - xxlx3exxb99 - DEV_PROJE 27.04.2021 11:18 27.04.2021 12:38 4,822 80 Transfer data error : Microsoft.DataTransfer.Common.Shared.HybridDeliveryException: ADLS Gen2 operation failed for: Object reference not set to an instance of an object.. Account: 'xxlx3exxb03'. FileSystem: 'xxxxxxxxx'.. ---> Microsoft.DataTransfer.Common.Shared.HybridDeliveryException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.IdentityModel.Clients.ActiveDirectory.Internal.Http.AdalHttpClient.<GetResponseAsync>d_22`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.IdentityModel.Clients.ActiveDirectory.Internal.Http.AdalHttpClient.<GetResponseAsync>d_22`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---

[Lingxi]

@obozdemir How is authentication configured in the CM and were you able to successfully execute the package in VS?