Running Your SSIS Packages in Azure: The Azure-SSIS Integration Runtime

Shannon Lowder

Shannon Lowder

— 4 min read

Every large data engineering shop I've worked with in the last ten years has SSIS packages. Some shops have hundreds of them — years of business logic encapsulated in .dtsx files, tested, documented (sometimes), and running reliably on on-premises SQL Server infrastructure. These shops look at Azure and face a real question: what do we do with all this SSIS investment?

The Azure-SSIS Integration Runtime in ADF v2 is Microsoft's answer. The pitch: your existing SSIS packages run in Azure with minimal modification. Let me tell you what that actually means in practice.

What the Azure-SSIS IR Is

When you provision an Azure-SSIS IR, Microsoft spins up a cluster of Azure VMs — you specify the VM size and count — and installs SQL Server Integration Services on them. These VMs are yours for the duration of the IR's running time. They connect to an Azure SQL Database or Managed Instance that hosts SSISDB, the SSIS catalog. Your .ispac packages deploy to this SSISDB exactly as they would deploy to an on-premises SQL Server SSISDB. ADF executes individual packages via the Execute SSIS Package Activity.

The workflow:

  1. Provision Azure SQL Database to host SSISDB
  2. Create Azure-SSIS IR in ADF, pointing it at the SQL Database
  3. Start the IR (20-30 minutes — go get coffee)
  4. Deploy your .ispac files to the SSISDB using SSMS or a deployment script
  5. Create ADF pipelines with Execute SSIS Package Activity
  6. Run

Provisioning the IR

The Azure portal has a wizard for this. The critical choices:

Node size: Standard_D4_v3 (4 vCPUs, 16GB RAM) is the minimum I'd run anything real on. If your packages do heavy in-memory transformations — large sort operations, complex lookups — go bigger. Standard_D8_v3 or Standard_D16_v3. You're paying per node per hour while the IR is running, so size it right rather than paying for idle capacity.

Node count: This determines how many packages can run concurrently. Two nodes with maxParallelExecutionsPerNode set to 4 means 8 concurrent package executions. For shops running dozens of packages on a schedule, think about your peak concurrency before picking this number.

SSISDB pricing tier: The SSISDB doesn't need to be large. S1 or S2 is sufficient for most shops — it's just catalog metadata, not bulk data. Don't over-provision it.

Deploying Packages

From SSMS: connect to the Azure SQL Database server using the SSISDB credentials you configured, navigate to Integration Services Catalogs, right-click SSISDB, Deploy Project. Same workflow as deploying to an on-premises SSISDB. Your .ispac files, built from your Visual Studio SSIS project, deploy exactly the same way.

For CI/CD pipelines, the ISDeploymentWizard command-line tool handles deployment:

ISDeploymentWizard.exe /Silent /SourceType:File /SourcePath:".\MyProject.ispac" `
  /DestinationServer:"yourserver.database.windows.net" `
  /DestinationPath:"/SSISDB/YourFolder/MyProject"

This is the same tool you'd use for on-premises SSIS deployment. No new tooling required.

Executing Packages from ADF

{
  "name": "ExecuteSSISPackage",
  "type": "ExecuteSSISPackage",
  "linkedServiceName": {
    "referenceName": "AzureSSISIR",
    "type": "IntegrationRuntimeReference"
  },
  "typeProperties": {
    "packageLocation": {
      "packagePath": "/SSISDB/YourFolder/MyProject/MyPackage.dtsx"
    },
    "environmentPath": "/SSISDB/YourFolder/MyEnvironment",
    "projectParameters": {
      "ServerName": { "value": "@pipeline().parameters.SourceServer" }
    },
    "packageParameters": {
      "OutputPath": { "value": "@pipeline().parameters.OutputPath" }
    }
  }
}

ADF can pass project and package parameters when executing. This is significant — it means you can drive SSIS package execution from ADF pipeline parameters, which closes some of the dynamic execution gap that SSIS has natively.

What Works Without Modification

Most SSIS functionality works as-is on the Azure-SSIS IR. Connection managers pointing to Azure SQL Database, Azure Blob Storage, Azure Data Lake Store — these work. On-premises connections via Self-Hosted IR — these require some configuration but work. Data transformation tasks (Derived Column, Aggregate, Sort, Lookup, Merge Join) — run identically to on-premises. Script Tasks in C# or VB.NET — work as long as the .NET code doesn't call local filesystem paths or local COM components.

What Requires Extra Work

Third-party components: if your packages use CozyRoc, Pragmatic Works, or other third-party SSIS components, those components must be installed on the IR nodes. Azure-SSIS IR supports custom setup scripts that run during provisioning — you can install MSIs and copy files using a script stored in Azure Blob Storage. It works, but it adds complexity to your IR provisioning and testing cycle.

Local file system access: packages that reference local file paths (C:DataInput, \servershare ile) need modification. Replace local paths with Azure Blob or Azure Files UNC paths. Azure Files can be mounted as a drive letter on the IR nodes, which handles some scenarios without package modification.

Windows authentication to on-premises SQL Server: Kerberos constrained delegation is required for this configuration. Doable, but it requires Active Directory setup that not every Azure environment has in place.

The Cost Reality

The Azure-SSIS IR runs VMs. Running VMs costs money continuously while they're running. A two-node Standard_D4_v3 cluster runs roughly $350-400/month if left running 24/7. For shops that run packages around the clock, this is just operational cost. For shops that run packages for two hours a day, paying 24/7 VM cost is wasteful.

The solution: start and stop the IR on a schedule. ADF has activities for this (or you can use Azure Automation). Start the IR 10 minutes before your package executions are scheduled, stop it when they're done. The startup time (20-30 minutes for first start, faster for subsequent starts with a warm node cache) is the constraint. Factor this into your scheduling.

Honest Assessment

The Azure-SSIS IR is a migration ramp, not a destination architecture. It lets you lift SSIS packages to Azure with minimal modification, which lowers the migration cost significantly for shops with large SSIS investments. That's genuinely valuable.

But running SSIS packages on VMs that you're paying for by the hour is not the long-term architecture. The direction is toward ADF native activities, Databricks notebooks, or Azure Functions for transformation logic — managed compute that scales to zero when not in use and doesn't require you to maintain a catalog and a VM cluster.

Use the Azure-SSIS IR to accelerate migration and de-risk the move to Azure. Plan to migrate critical packages to cloud-native approaches over 12-24 months. Don't treat it as permanent. If you're scoping an Azure-SSIS IR migration for your SSIS estate, I'm here to help.

Read more