Getting Third-Party SSIS Components onto the Azure-SSIS Integration Runtime

A third-party component is the most common reason a lift-and-shift stalls. The package opens fine on the old server. On the Azure-SSIS integration runtime (IR) it fails validation with a missing component error. This post covers how to find those components before the move, and how to get them onto the IR.

We do not know your vendor mix. Treat the steps as a method, and measure on your own estate.

Step 1: Inventory what is actually used

The Data Migration Assistant (DMA) and the Azure Data Factory assessment tooling report on what they can recognise. They are a start, not a full list. Do your own pass too.

A .dtsx file is XML. Every component in a data flow carries a componentClassID, and every connection manager carries a CreationName. Search the project folder for them:

Select-String -Path .\*.dtsx -Pattern 'componentClassID="([^"{]+)"','CreationName="([^"]+)"' -AllMatches |
  ForEach-Object { $_.Matches } | ForEach-Object { $_.Groups[1].Value } |
  Group-Object | Sort-Object Count -Descending

Built-in components show as Microsoft.* class names or as GUIDs. Anything else, such as a vendor namespace or a CreationName you do not recognise, goes on the list. For each entry record:

  • the vendor, product and installed version on the current server;
  • the number of packages that use it;
  • whether those packages run nightly, monthly or almost never;
  • whether the licence is per server, per core or per user.

Two things the XML search catches that assessments often do not: components referenced only inside a Script Task through an assembly reference, and custom .dll files loaded from a path in a package configuration.

Step 2: Read the DMA output for what it is

A flagged component means DMA could not confirm support. An unflagged component does not mean it will run. Sort your list into three groups:

  1. Vendor supports the Azure-SSIS IR. Some vendors document an installer or a script for it.
  2. Vendor is silent. The component may still install. You test it.
  3. Component is abandoned or the vendor is gone. You need a replacement or a retirement decision.

Group 3 is where the retire, lift-and-shift or rewrite call is made for those specific packages. A rewrite of five packages that depend on a dead connector is a different cost from a rewrite of the estate.

Step 3: Install through custom setup

The Azure-SSIS IR is a managed set of nodes. You cannot remote in and run an installer by hand. The supported route is a custom setup: you place a script and its payload in an Azure Blob Storage container, and the IR runs it on every node at start.

The pieces:

  • A blob container holding a main.cmd file at the root. The IR runs this file on each node.
  • The installer files, drivers or assemblies in the same container.
  • A SAS URI for the container, with read, write and list permission, supplied in the IR settings.
  • A main.cmd.log output file, which the IR writes back to the container. Read this first when a start fails.

A minimal main.cmd for a vendor MSI installer:

@echo off
set LOGFILE=%CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log
msiexec /i VendorConnector_x64.msi /qn /l*v %LOGFILE% LICENSEKEY=%VENDOR_KEY%
if errorlevel 1 exit /b 1

Notes from practice:

  • Install the 64-bit build. The IR runs packages in 64-bit mode, and a package that relied on a 32-bit-only driver will need a 64-bit one.
  • Do not hard-code a licence key into the script if you can avoid it. Keep it in the container with restricted access, or in Azure Key Vault where the vendor allows it.
  • A custom setup script runs on every node at every start. An installer that is slow adds to every start time. Keep the payload small.
  • Some components need a licence tied to hardware or server name. Nodes are replaced and renamed. Ask the vendor how licensing works on an elastic, ephemeral host before you sign anything.
  • Components that only work with a GAC install, a registry key or an .snk-signed assembly may need extra lines in main.cmd. Test each one.

Step 4: Test with a start, not with a validate

The sequence that avoids surprises:

  1. Start a small dev IR, one node, with the custom setup.
  2. Read main.cmd.log. Confirm exit code 0.
  3. Deploy one package per component to the dev SSISDB or run it from a package store.
  4. Run it against a copy of the data, not production.
  5. Compare output with the old server's output. Our reconciliation method is in the parallel-run post linked below.

A package that validates and then fails at run time usually points to a driver version difference or a missing downstream dependency, such as an ODBC DSN that existed only on the old server.

Step 5: Cost and start time

Every node pays for the custom setup time at start. If the IR is scheduled to start and stop around the nightly window, build the setup duration into the start time. The cost control post covers the schedule side.

When the answer is not to install it

Sometimes the honest outcome is a different path:

  • Keep the package on SQL Server. If one nightly package depends on an unsupported component and everything else is moving, leaving that package on a small SQL Server VM is legitimate. It does carry its own patching burden.
  • Replace the component. Many connectors have a built-in equivalent in a Data Factory copy activity or an ODBC or REST source. The component mapping post lists where that holds.
  • Retire the package. Check the execution history in SSISDB first. A package that has not run in a year is a question for its business owner, not an engineering task.

What to bring to a conversation

If you want a second pair of eyes, send us the component list from step 1, the SQL Server version you run, the number of packages, and what is driving the move. For teams on SQL Server 2016, the extended support end date is 14 July 2026, so a blocker list is worth having early. A senior engineer replies within a day. The usual start is an SSIS Estate Assessment, which includes this inventory at package level.

Related posts: Mapping SSIS Components to Azure Data Factory Activities, Controlling Azure-SSIS Integration Runtime Cost, Reconciling a Parallel Run.