Azure SSIS Integration Runtime: Execute Packages with On-premise Data

Executing SSIS Package that connects to on-premises data with Azure SSIS Integration Runtime (Architecture)

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 may earn some commission if you purchase through them. This way you can support my blog at not extra cost to you!

 

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)

1. Join Azure SSIS Integration Runtime (3 instances) to a subnet in Azure VNet. The box below indicates a SQL Server behind a firewall
1. Join Azure SSIS Integration Runtime (3 instances) to a subnet in Azure VNet. The box below indicates a SQL Server behind a firewall

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

2. Creating a Virtual Network Gateway (a VM) in the Gateway Subnet
2. Creating a Virtual Network Gateway (a VM) in the Gateway Subnet

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

Connecting on-premises network with Azure Virtual Network
3. Connecting the on-premises network with Azure Virtual 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

Migrating SSIS Packages to Azure Data Factory

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


Want to be notified as soon as I post? Subscribe to 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.

  •  
  •  
  •  
  •  
  •  
  •  

You may also like

Leave a Reply

Your e-mail address will not be published. Required fields are marked *