ADF vs SSIS: Migrating Legacy ETL to the Cloud

For twenty years, SQL Server Integration Services has been the tool that loads the warehouse overnight in organisations that run on SQL Server. When the databases move to Azure, the packages do not come along by themselves, and "rewrite a thousand packages" is not a plan anyone will fund. ADF vs SSIS is therefore rarely a product comparison. It is a migration question: which packages move as they are, which get rebuilt, and in what order.

What each one is

SQL Server Integration Services is a platform for building data integration and transformation solutions, installed as part of SQL Server. Packages are designed graphically, and deployed to an SSIS Catalog database that stores, runs and manages them (Microsoft Learn: SQL Server Integration Services).

Azure Data Factory is a cloud service for the same broad purpose, moving and transforming data on a schedule, built in a different era and on different assumptions.

The row that matters most is the transformation engine. SSIS pulls rows into its own memory, transforms them, and writes them out: classic ETL. Data Factory's natural pattern is to copy data to the destination and transform it there, or on a Spark cluster. That is ELT, and it is why a faithful one-to-one translation of a package is often the wrong goal. ETL vs ELT explains the difference.

Route one: lift and shift

Data Factory can run SSIS packages unchanged. It has a third kind of integration runtime for exactly this, the Azure-SSIS integration runtime: a managed cluster of machines whose job is to execute packages (Microsoft Learn: lift and shift SSIS workloads).

How it fits together:

• The runtime is provisioned in a data factory. You choose the node size and the number of nodes.
• The catalog, SSISDB, lives in Azure SQL Database or SQL Managed Instance. Microsoft describes this separation of storage from runtime as the most significant difference from SSIS on premises.
• Packages are still designed in SQL Server Data Tools and deployed with the familiar tools.
• Execution can be started from those tools, or from a pipeline with the Execute SSIS Package activity, which lets Data Factory triggers and monitoring take over scheduling (Microsoft Learn: Execute SSIS Package activity).

Details that matter in planning:

• Deployment model. Deploying to SSISDB in Azure requires the project deployment model. Packages on the older package deployment model can run from the file system, Azure Files or MSDB in a managed instance (Microsoft Learn: create an Azure-SSIS integration runtime).
• Versions. A package from any SSIS version can be deployed, and is upgraded automatically to the latest package format if validation passes.
• Reaching on-premises data. The runtime can join a virtual network, or use a self-hosted integration runtime as a proxy.
• Third-party components and drivers have to be installed on the runtime through its custom setup. A package that depends on a component with no cloud installation is a blocker.
• Cost behaves like a server, not like a pipeline. The runtime is a cluster that bills while it runs. Microsoft's advice is to run it only when needed, starting and stopping it on a schedule.

Lift and shift is a migration of where packages run, not of what they are. The team keeps its skills and its tested logic. It also keeps a server-shaped cost and a tool whose design centre is the past.

Route two: rebuild in Data Factory

Rebuilding means expressing the same outcome with pipelines. The concepts map across, loosely:

Two warnings about this table.

Do not translate; redesign. Many packages contain long chains of row-by-row transformation that exist because the SSIS engine was the only place to do the work. In the cloud, load the raw data and express the logic as one SQL statement in the warehouse. It is usually shorter, faster and easier to read.

Script tasks are the hard part. Anything written in .NET inside a package has no automatic equivalent. Inventory these first; they decide the effort.

Mapping data flows in ADF covers the visual transformations that most resemble an SSIS data flow.

The migration process

Microsoft describes two phases, assessment and migration (Microsoft Learn: migrate on-premises SSIS workloads).

Assessment. Find what would stop a package running on the Azure-SSIS runtime. The documentation separates migration blockers from informative issues, such as partly supported or deprecated features. One caution: the page describes the Data Migration Assistant for this step and also notes that the tool is deprecated, so check which assessment tool Microsoft currently recommends.

Where the packages are stored decides the route. The same page lists four storage types and the path for each:

Schedules are a second migration. SQL Server Agent jobs do not exist in Azure SQL Database. They either move to the agent in SQL Managed Instance or become Data Factory pipelines with triggers. The second is the better end state, because it puts SSIS runs and native pipelines under one scheduler and one monitoring view.

Deciding, package by package

Few organisations should pick one route for everything. Sort the packages.

A workable sequence:

1. Inventory: every package, what it reads and writes, how often it runs, when it last changed.
2. Assess for blockers.
3. Lift and shift the bulk, to meet the date for moving off the servers.
4. Move scheduling into Data Factory, with the Execute SSIS Package activity.
5. Rebuild in priority order: the simplest and the most frequently changed first.
6. Shrink the SSIS runtime as packages are retired, and stop it when the last one goes.

The point of step 3 is to separate two risks. Moving infrastructure and rewriting logic at the same time means that when a number is wrong, nobody knows which change caused it.

Where Fabric leaves SSIS

Microsoft now describes Data Factory in Fabric as the next generation of Azure Data Factory, so it is fair to ask what that means for SSIS. The Fabric upgrade guidance, for workloads that include SSIS, is to execute the packages with Azure Data Factory pipelines and call those from Fabric (Microsoft Learn: upgrade planning for Fabric). In other words, Azure Data Factory remains the home for SSIS in the cloud for now. Microsoft Fabric vs Azure Data Factory covers the wider picture.

That is one more argument for treating lift and shift as a stage, not a destination. Each package rebuilt as a native pipeline is one fewer tied to a runtime with an uncertain long-term path.

Mistakes to avoid

• Rewriting everything before moving anything. The server lease expires first.
• Lifting everything and stopping. The runtime's cost continues, and nothing has been modernised.
• Leaving the SSIS runtime on around the clock for packages that run for an hour a night.
• Translating packages task by task. Redesign for ELT.
• Forgetting the schedules, the agent jobs, their dependencies and their alerts.
• Skipping reconciliation. Run old and new side by side and compare outputs before switching off the old.

For the runtime types, see integration runtimes in ADF; for reaching SQL Server from the cloud without SSIS, copying data from on-prem SQL.

Azure Data Factory, SQL and ETL pipeline design are covered in the Data Engineering course.

Sources

• SQL Server Integration Services (Microsoft Learn) — Microsoft Learn
• Lift and shift SQL Server Integration Services workloads to the cloud (Microsoft Learn) — Microsoft Learn
• Migrate on-premises SSIS workloads to SSIS in ADF or Synapse Pipelines (Microsoft Learn) — Microsoft Learn
• Create an Azure-SSIS integration runtime in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Run an SSIS package with the Execute SSIS Package activity (Microsoft Learn) — Microsoft Learn
• Upgrade planning for Azure Data Factory to Fabric Data Factory (Microsoft Learn) — Microsoft Learn