Motivation #

When running SSIS packages on an Azure SSIS-Integration Runtime (IR), it is very likely that you are using Connection Managers that connect to on-premises data sources or targets. For example, with Xtract IS for Azure this could be an SAP system hosted on-premises in your company network.

To overcome the challenge of connecting to on-premises resources from a cloud environment, SSIS-IR offers two different solutions.

  1. Configure a VPN tunnel (VNet) from Azure to your on-premises resource Join an Azure-SSIS integration runtime to a virtual network
  2. Set up a self-hosted IR to act as a proxy for your Azure SSIS-IR. Configure a self-hosted IR as a proxy for an Azure-SSIS IR in Azure Data Factory

Xtract IS for Azure supports both options. Actually, it supports those options pretty much out of the box. So, that’s good news for you. The second option requires XIS for Azure version 6.5.10 or higher.

Goal #

This article describes how to enable usage of Xtract IS for Azure with a self-hosted IR. This article is a supplement to Microsoft’s documentation on How to Enable custom/3rd party components for usage with a self-hosted IR.

Let’s get started #

Read the Microsoft documentation about configuring a self-hosted IR as a proxy for an Azure-SSIS IR in Azure Data Factory and implement the steps mentioned therein. That’s the major part of the work you need to do. And you would need to do this, irrespective of whether you use Xtract IS for Azure or not.

  1. Prepare the self-hosted IR
  2. Prepare the Azure Blob Storage linked service for staging
  3. Configure an Azure-SSIS IR with your self-hosted IR as a proxy
  4. Enable SSIS packages to connect by proxy

In the beginning, I was a bit confused about step 1., Prepare the self-hosted IR. I need a Windows server that has access to my on-prem SAP system, that’s clear. But, what software, other than the Integration Runtime needs to be installed on that machine? A SQL Server? SSIS? VS/SSDT? The answer is: None of that. Just the Integration Runtime and, obviously, Xtract IS for Azure…but we’ll come to that in a bit.

I performed steps 2 and 3 following above mentioned Microsoft documentation. The outcome looks something like this in the Azure portal.

Configure_SSIS-IR_for_SHIR

Step 4 requires you to set the ConnectByProxy property in the XTRACT connection manager to true. Attention: This property won’t show in older releases of SSDT, if that’s the case, please update.

ConnectByProxy

Now, continuing where we left off with step 1. we need to install Xtract IS for Azure on our self-hosted IR.

Remember when I said, you just need a plain vanilla Windows server, w/o SSIS, SQL Server or SSDT pre-installed? Well, this still holds true..although, if you want, you can have these tools installed. But, let’s pretend they are not.

The Xtract IS for Azure setup routine expects certain SSIS/DTS folders to be in place on the server, because that is where the Xtract DLLs are placed during setup. These SSIS/DTS folders usually come with an installaion of SSIS/SSDT.

Question: But what happens, when no SSIS/DTS folders are available on that server?

Answer: Well, we need to pretend, they are.

Question: And how do we do that?

Answer: Enter Regedit or the Registry Editor. This tool allows entering Registry Keys which make Xtract IS for Azure believe, SSIS/DTS is installed on the server. This is what the Microsoft documentation describes here

Create the following DTSPath registry keys on self-hosted IR if they don’t exist already: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\140\SSIS\Setup\DTSPath and Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\140\SSIS\Setup\DTSPath.

I shall add, that the DTSPath key needs to have assigned these values: C:\Program Files\Microsoft SQL Server\140\DTS\ and C:\Program Files (x86)\Microsoft SQL Server\140\DTS\

Regedit

After you have entered these registry keys, you can install Xtract IS for Azure. And that’s all you need to do. Xtract IS for Azure takes care of the rest.

Deploy and run SSIS packages #

If you (correctly) performed all the steps mentioned above, you can then deploy your SSIS packages to the Azure SSIS-IR. Even if your XTRACT connection manager points to an on-premises SAP system connection to that system will be established from the SSIS-IR. Make sure your Azure SSIS-IR and Self-Hosted IR are both running.

Azure_IRs

The SSIS reports can be found on your local self-hosted IR in the following folders…just in case Theobald Software tech. support is asking for it:

Logs