SSIS Integration Runtime Connectivity Testing

SSIS Integration Runtime Connectivity Testing is hard as there is no physical Azure VM to log in to as part of the Azure Data Factory (ADF). While behind the scenes there is effectively a VM spun up there is no way to access it.

The scenario our Data Platform team faced was reasonably simple – we needed to connect to a 4D database that sat behind the Storman application that our customer used so that we could extract data for their various workloads. Because 4D is not supported by the Generic ODBC source in Azure Data Factory, we needed to use the 4D ODBC driver. This meant using SSIS to leverage the driver.

The client is well managed in terms of security so the target system can only be accessed within their network. Their Azure network was connected to theirs and properly secured, so part of the setup of the SSIS Integration Runtime in Azure Data Factory is to ensure that it is joined to the virtual network.

Houston, we have a problem

SSIS Azure Data Factory

However, despite all this – we couldn’t get the ODBC connection to work when deployed. Due to stability issues our first suspect was the driver – after all it frequently crashed Visual Studio 2017 / SSDT and configuration was a pain. Also, initially we couldn’t connect on our dev machines as we weren’t on the clients VPN (easily fixed, fortunately). Then we had the wrong target server (again easily fixed).

Once we got on to ADF of course our debugging options got more limited as we now were having to do SSIS Integration Runtime Connectivity Testing without all the tools available on our desktops . Initially we struggled because the runtime was very slow at sharing its metadata (package paths, connection managers, etc.) so we weren’t initially sure it was even able to work with the driver. Eventually we got enough metadata to start playing with the JSON of the task to configure it. However we continued to get errors in ADF that were’t really helping.

Our breakthrough came when we remembered we could just connect to the more familiar and mature environment of the SSIS catalog that is deployed alongside the runtime. We configured the package correctly, ran it and got a more manageable ODBC error – “Cannot reach Destination server”. A quick ping from our desktops proved the server could be pinged, so as a test we used a simple package with just a script task to ping the server. This worked just fine on our desktop, but when deployed the script task reported failure.

So a quick connectivity test helped pin it down to probable network config issue. Now it’s in the Infrastructure teams hands to ensure everything is configured correctly, but at least we have (for now at least) got SSIS & the ODBC driver off the list of probable causes of the issue. It’s also taught us a few things about SSIS Integration Runtime Connectivity Testing.