How to Migrate SSIS Packages to Azure Data Factory
“Migrate SSIS to ADF” is not one project. It is an assessment of packages, schedules, dependencies, security, and operating requirements followed by a deliberate choice: run existing packages on Azure-SSIS Integration Runtime or rebuild selected flows as native pipelines.
1. Assess every package and schedule first
Inventory where packages live—SSISDB, MSDB, file system, or package store—plus SQL Server Agent jobs, proxies, environment references, configuration files, credentials, UNC paths, third-party components, custom assemblies, and external data sources. Microsoft’s current SSIS migration overview separates assessment from migration and documents different steps by package-storage type.
- Package/project name, owner, business purpose, schedule, duration, and data volume.
- Source and destination systems, authentication method, network path, and region constraints.
- Script Tasks, custom components, drivers, 32-bit dependencies, local files, UNC paths, and sensitive parameters.
- Upstream/downstream jobs, restart behavior, alerting, support owner, RPO/RTO, and cutover window.
2. Choose the migration path package by package
- Lift-and-shift via Azure-SSIS Integration Runtime — your existing .dtsx packages run largely unchanged, hosted in an Azure-SSIS IR that's provisioned and managed through Azure Data Factory. Minimal rework, fastest path to "running in Azure."
- Native rebuild in ADF — pipelines are recreated using ADF's own activities (Copy Activity, Mapping Data Flows, and orchestration), replacing SSIS constructs rather than hosting them. More upfront work, but cloud-native monitoring, scaling, and long-term maintainability.
3. When lift-and-shift is the right call
Favor Azure-SSIS IR when packages are complex (heavy use of Script Tasks, custom components, or intricate control flow), the timeline is tight, or the goal is simply getting out of an on-premises SSIS server without a full re-engineering project. It's also a reasonable intermediate step — get running in Azure first, rebuild the highest-value pipelines natively later, on your own schedule.
4. When a native rebuild is the right call
Favor rebuilding in ADF when packages are relatively simple, you want to take advantage of ADF's broader connector ecosystem and native monitoring/alerting, or the long-term plan is to move away from SSIS entirely rather than carry it forward indefinitely. Native pipelines are also easier to hand off to a team that doesn't have deep SSIS background, since ADF's authoring model is more approachable.
5. Component mapping — the practical cheat sheet
| SSIS component | ADF equivalent |
|---|---|
| Data Flow Task | Copy Activity (simple movement) or Mapping Data Flow (complex transformations) |
| Control Flow (Sequence, For Each Loop, etc.) | Pipeline activities: ForEach, If Condition, Execute Pipeline |
| SSIS variables and parameters | ADF pipeline and dataset parameters |
| Package Configurations / connection strings | Linked Services, ideally backed by Azure Key Vault |
| Execute SQL Task | Stored Procedure Activity or Script Activity |
| Script Task / custom .NET components | No direct equivalent — typically an Azure Function, Databricks notebook, or custom activity |
6. What commonly breaks or gets missed
- Custom Script Tasks and third-party components — these have no drop-in ADF equivalent and usually require rebuilding the logic as an Azure Function or, for heavier transformation logic, a Databricks notebook.
- SSISDB-based logging and reporting habits — teams used to querying SSISDB for execution history need to rebuild that reporting against ADF's own monitoring and Azure Monitor integration; it doesn't map one-to-one.
- Error handling patterns — SSIS's event handlers and precedence constraints don't translate directly; ADF's retry policies and activity dependency conditions (Succeeded/Failed/Completed/Skipped) need to be deliberately rebuilt, not assumed equivalent.
- Absolute and UNC configuration paths — Microsoft’s migration rules explicitly flag these because Azure-SSIS IR may not be able to reach or interpret them as the original host did.
- SQL Server Agent schedules — package migration does not automatically preserve operating behavior. Microsoft documents an SSMS wizard that can convert qualifying SSIS job steps into ADF pipelines, activities, and schedule triggers.
- Performance characteristics — a package tuned for an on-premises SSIS server's memory and I/O profile may behave differently under Azure-SSIS IR sizing, and Mapping Data Flows have their own performance model.
7. Test in parallel before cutover
Run the old and new pipeline against the same source data and compare outputs row-for-row (or at minimum, aggregate-level) before retiring the original. This catches silent logic differences — a transformation that "looks right" in ADF but handles a null, a data type conversion, or an edge case differently than the original SSIS package did.
8. Make monitoring and rollback part of “done”
Define the production signals before cutover: package/pipeline success, row counts, duration variance, source-to-target reconciliation, retry exhaustion, and business freshness. Azure-SSIS IR and native ADF pipelines have different monitoring surfaces; decide who watches each one and how failures enter the existing incident process.
Keep the old schedule disabled but recoverable until reconciliation and the agreed observation window complete. A rollback is not “restore the old package.” It includes credentials, schedules, connection routes, input replay, duplicate-processing controls, and communication to downstream owners.
9. Migrate in waves
Group packages by dependency chain and business criticality. Pilot a representative low-risk wave, correct the deployment and monitoring pattern, then move higher-impact chains. It is common to lift-and-shift complex packages first and rebuild simpler, high-value flows natively where the long-term return is clearest.
The migrations that go badly aren't usually the technically hard ones — they're the ones where nobody decided up front which packages get lifted and which get rebuilt, and the approach gets picked package by package under deadline pressure.
Sitting on a pile of SSIS packages and not sure where to start?
Free 20-minute fit call — bring your package inventory, get a straight read on lift-and-shift vs. rebuild.
Book a free fit call