Packages with SSIS Integration Runtime
There are huge benefits to migrating SSIS Packages to cloud-like Scalability, Pricing, and Flexibility. After you migrate the SSIS Packages to Azure Data Factory, all seems well, if the packages access only the public cloud data stores with Azure SSIS Integration Runtime. Their execution is very straight forward.
But, if the packages access on-premises data (like SQL Server), not everything is a rosy picture. We need to do a few tweaks to get around our way. In this post, I will cover these tweaks so you can successfully setup package execution with on-premises data.
If you like to watch a video to breeze through the topic, I embedded a video toward the bottom of the post.
Full Disclosure: Some of the links in this post are affiliate links. I receive a commission when you purchase through them.
Steps to Execute Packages Connecting to On-premises Data with Azure SSIS Integration Runtime
If you execute SSIS Packages that connect to cloud data stores with Azure SSIS Integration Runtime (IR), you would use either of the 2 activities:
- Execute SSIS Package Activity
- Stored Procedure Activity
But, if the SSIS Packages connect to the data in the on-premises data store (i.e. SQL Server), direct execution will not work, as the data is behind a firewall and SSIS Integration Runtime does not have a direct line of sight to the data.
There are 3 steps(tweaks) that are necessary:
- Join the Azure SSIS Integration Runtime to a Virtual Network (VNet)
- Create a Virtual Network Gateway in a Gateway Subnet in the same VNet
- Establish a connection between VNet and on-premises network
1. Join the Azure SSIS Integration Runtime to a Virtual Network (VNet)
First, you need to join the Azure SSIS IR to a VNet subnet. Connections to Azure SQL Database/Managed Instance remain intact. This step also creates additional Azure resources like a Load balancer, Network Security Groups to successfully establish the connections between Data Factory, Integration Runtime, and SQL Database (where SSIS catalog is hosted). This step is necessary as you will join the VNet (with the IR) to the on-premises network.
2. Create a Virtual Network Gateway in a Gateway Subnet in the Same VNet
Next, you need to create a VNet Gateway in a gateway subnet in the same VNet. This gateway handles traffic from the on-premises network and directs them to the Azure SSIS IR. And vice-versa.
3. Establish a Connection between VNet and On-premises Network
There has to be some way that the on-premises network/client and the VNet could talk with each other. You have the following options:
- A Point-to-Site VPN (for joining a single computer to VNet over the Internet)
- A Site-to-Site VPN (to join the on-premises network to VNet over the Internet)
- An ExpressRoute connection (faster, reliable private connection)
With this connection in place, the SSIS IR will be able to access on-premises data with either Windows authentication or SQL authentication.
Here is a video from my YouTube channel, covering the steps
In addition to these steps, you also need to ensure to deploy a custom DNS server in the VNet (for resolving on-premise server name) or use the IP address given to the on-premise server by the VNet gateway.
In conclusion, these are the 3 steps to successfully execute an SSIS package that connects to SQL Server/on-premise data store with Azure SSIS IR.
If you find any issues in setup, view the complete demo in action, with this Pluralsight course, where I have demonstrated each step with a little more intuition in some detail.
As always, please leave your doubts in the comment section.
Follow Me to Receive Updates on Azure
Want to be notified as soon as I post? Subscribe to the RSS feed / leave your email address in the subscribe section. Share the article to your social networks with the below links so it can benefit others.
