Azure Data Factory · Azure Data Platform · Azure Services · Data Engineering · Data Management · Data Migrations

Lift and Shift SSIS Package in ADF

In this blogpost, we are going to see how the SSIS package runs in Azure Data Factory at high level with an example. Here, we will create Sample SSIS Package first and then create ADF instance with Azure-SSIS Integration Runtime and using Execute SSIS package activity within Pipeline.

Prerequisite

  • Active Azure Subscription. If you don’t have, create a free account.
  • Subscription Level Contributor access or Owner access with basic understandings of using SSIS/ADF and its components as well as creating simple SSIS Package as well as ADF instance. we can check our Access in Subscriptions–>Access Control (IAM)–>View my Access. (like below)
Type Subscriptions in Global Search Resources Text Box
Check our Access at Subscription level

1 – Sample SSIS Package

sample SSIS Package

We can go with either existing SSIS package that we have or create the new one like above, assuming users/viewers have basic know-how to create above simple SSIS package in Visual Studio. For simplicity, here we have created simple SSIS package using Microsoft Visual Studio-2017[SSDT] that performs data transformation of a single table data from source database[Azure SQL Database] to destination database[Azure SQL Database].

Note : If we are using pretty old Visual Studio versions for our SSIS package, Try to update and match with some latest versions for the compatibility reasons. Also here we have used Azure SQL Single Databases only for both Source as well as Destination DB since they are easy and take less time to create and also set the pricing tiers for both DB’s as Basic[DTU Model]. Also make sure, respective firewall permissions[Allow All Azure Services to access as well as add client IP address] added while using Azure SQL DB based on our security related requirements.

2 – create Azure-SSIS IR

Integration Runtime with Azure-SSIS IR settings summary
  • Create SSIS catalog (SSISDB) hosted by Azure SQL Database server/Managed Instance to store your projects/packages/environments/execution logs. Here we have used Azure SQL Single DB for simple demo purpose, we can also able to choose/use the Managed Instance with VNet kind of security.
  • Also please aware of the cost estimates for running Azure-SSIS IR in settings page and stop the Azure-SSIS IR once the purpose is done. Also make change the status of Azure-SSIS IR as ‘Running‘ while executing the package within ADF.

3 – Build and Deploy our SSIS Package in SSIS catalog (SSISDB)

From the Visual Studio, right click on the project and build the project. Also make sure that our project is in Project Deployment Model. Once the build is done successfully and then right click again and deploy the project now by choosing the destination of our deployment as SSIS catalog (SSISDB).

Deployment of SSIS package in SSISDB catalog [at SSMS]

Before execution, please check the status of Azure-SSIS IR is set as Running and the nodes are available for making this execution as successful run. Then, in SSMS itself we can execute like above and check whether the data transformations are happening correctly.

4 – ADF Pipeline

Pipeline with Execute SSIS package activity

Here, in this Pipeline, only 1 activity is required and that is Execute SSIS package activity.

Execute SSIS package Activity – settings
Execute SSIS package activity and settings

Make the activity settings as above and keep rest of the settings as default as it is. Once everything is done, Debug it, then Trigger / Publish it. Use Trigger types[Trigger now option, Schedule, Tumbling Window, Storage Events and Custom events(Preview)] as per requirement.

Please watch, give STAR/Fork – AzureStuffs Repos.

Recent Related Posts

Azure Resource Lock

We can even apply Azure Resource Lock that prevents accidental deletions and modifying of the resources.

Summary

Thus, we saw how to lift and shift the SSIS package in Azure Data Factory at high level and other settings as well, regarding Azure-SSIS IR. Here we have used Azure Services like Azure Data Factory and Azure SQL Databases[Single DB] and Microsoft’s SQL Server Integration Services.

Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

2 thoughts on “Lift and Shift SSIS Package in ADF

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s