Migrate SQL Server Integration Services packages to Azure Data Factory pipelines without losing transformations, schedules, or data integrity. FreedomDev has migrated 500+ SSIS packages across manufacturing, healthcare, and financial services companies moving their SQL Server workloads to Azure. We handle the SSIS Migration Assistant, manual pipeline recreation, Azure-SSIS Integration Runtime provisioning, and the hybrid approaches that Microsoft documentation never covers.
SQL Server Integration Services was the right answer for on-premises ETL in 2005. Two decades later, it is the single biggest obstacle preventing data teams from completing their Azure migration. SSIS packages run on Windows servers that require SQL Server licenses, SSISDB catalog databases, and an Integration Services runtime that does not exist natively in Azure SQL Database or Azure Synapse Analytics. When your organization moves its SQL Server instances to Azure SQL Database or Azure SQL Managed Instance, every SSIS package that loads data, transforms records, or orchestrates nightly batch jobs suddenly has nowhere to run. The packages still work. The servers they run on are scheduled for decommission.
The migration pressure is not theoretical. Microsoft ended mainstream support for SQL Server 2014 in 2024 and SQL Server 2016 reaches end of extended support in 2026. Companies running SSIS on these versions face a hard deadline: migrate the packages or pay for Extended Security Updates at roughly 75% of the license cost per year. For a company with 200 SSIS packages running on 3-4 SQL Server instances, the annual ESU cost alone can exceed $120,000 before you account for the Windows Server licenses, storage, and the DBA time to maintain aging infrastructure. The math stops working once Azure SQL Database can host your relational workloads at a fraction of the cost with zero patching overhead.
The real pain is not the cost. It is the risk of doing the migration wrong. SSIS packages accumulate undocumented business logic over years. A package that looks like a simple data load from a flat file often contains 40-60 data flow transformations, conditional splits based on business rules that only one DBA understands, script tasks with C# code that calls internal APIs, and event handlers that send failure notifications to distribution lists that no longer exist. Migrating these packages requires understanding what each one actually does, not just what its name implies. Teams that attempt a lift-and-shift without auditing package logic end up with Azure Data Factory pipelines that run without errors but produce wrong data, or worse, Azure-SSIS Integration Runtime clusters that cost $800-2,000/month to keep legacy packages running in the cloud with zero modernization benefit.
The three migration paths each come with distinct tradeoffs. The SSIS Migration Assistant automates conversion of simple packages but chokes on script tasks, custom components, and complex expressions. Manual pipeline recreation in ADF gives you native cloud performance and pay-per-use pricing but requires rebuilding every data flow from scratch. The hybrid approach using Azure-SSIS Integration Runtime lets you run existing packages unchanged in Azure but ties you to a dedicated compute cluster that bills by the hour whether your packages are running or not. Most real migrations use all three approaches for different subsets of packages. The question is which packages go where, and that decision requires a detailed assessment that most teams skip.
SSIS packages have no execution environment in Azure SQL Database or Azure Synapse — they require a Windows server or Azure-SSIS IR
SQL Server 2014/2016 end-of-support forces migration or $120K+/year in Extended Security Updates for typical 3-4 server deployments
Undocumented business logic buried in Script Tasks, custom components, and Expression Language makes packages opaque to anyone except the original author
Azure-SSIS Integration Runtime costs $800–$2,000/month for a dedicated cluster that runs 24/7 whether packages execute or not
SSIS Migration Assistant cannot convert Script Tasks (C#/VB.NET), custom data flow components, or complex Expression Language formulas
Failed migrations produce pipelines that execute without errors but generate wrong output data due to missed transformation logic
Our engineers have built this exact solution for other businesses. Let's discuss your requirements.
FreedomDev approaches every SSIS-to-ADF migration by categorizing packages into three tiers based on complexity, and applying the right migration strategy to each tier. Tier 1 packages are simple data movers: they extract from a source, apply minimal transformation, and load into a destination. These convert cleanly with the SSIS Migration Assistant or can be rebuilt as ADF Copy Activities with mapping data flows in 1-2 days per package. Tier 2 packages contain moderate business logic: conditional splits, derived columns, lookup transformations, slowly changing dimension handling, and moderate Expression Language usage. These require manual pipeline construction in ADF using mapping data flows, but the logic translates directly once you understand the package. Tier 3 packages are the ones that break every automated tool: Script Tasks running hundreds of lines of C#, custom components compiled from third-party DLLs, complex event handlers, and packages that call other packages in execution chains. Tier 3 packages either run on Azure-SSIS Integration Runtime unchanged, get rewritten as ADF pipelines with Azure Functions handling the custom logic, or get decomposed into smaller components that can be individually migrated.
Azure Data Factory is not a 1:1 replacement for SSIS. It is a fundamentally different paradigm. SSIS is a desktop-designed ETL tool where you build data flows visually in Visual Studio, deploy packages to an SSISDB catalog, and schedule them with SQL Server Agent. Everything runs on a single server. ADF is a cloud-native data orchestration service where pipelines define the control flow, activities execute individual operations, datasets describe your data structures, linked services manage connections, and integration runtimes provide the compute. The execution model is serverless by default: you pay per activity run and per data movement unit, not for an always-on server. This architectural difference means that migrating SSIS packages is not a format conversion exercise. It is a re-architecture of how your data pipelines execute, scale, and recover from failure.
The feature mapping between SSIS and ADF is where most teams get confused. SSIS control flow maps to ADF pipeline activities: Execute SQL Task becomes a Stored Procedure activity, File System Task becomes a Copy activity or Delete activity, For Each Loop Container becomes a ForEach activity, Sequence Container becomes a group of activities with dependency chains. SSIS data flow is more complex: it maps to ADF mapping data flows for transformations (joins, aggregates, derived columns, conditional splits, slowly changing dimensions) or to Copy activities for simple source-to-destination movement. SSIS connection managers become ADF linked services. SSIS parameters and variables become ADF parameters and variables, though the expression syntax is completely different: SSIS uses its own Expression Language (similar to C# syntax), while ADF uses a combination of string interpolation and its own expression language with functions like @concat(), @utcnow(), and @pipeline().parameters.parameterName. Every expression in every package must be manually translated.
Connection manager migration is deceptively complex because the authentication models differ. SSIS connection managers typically use Windows Authentication or SQL Server Authentication with credentials stored in the SSISDB catalog or package configuration files. ADF linked services use Azure Key Vault references, managed identity authentication, or service principal authentication. An SSIS package that connects to an on-premises SQL Server via Windows Auth using a domain service account needs an ADF linked service that either uses a self-hosted integration runtime with stored credentials, or requires the on-premises SQL Server to be accessible via Azure Private Link or VPN Gateway. Flat file connections in SSIS that reference local file paths (C:\ETL\Inbound\daily_orders.csv) must be converted to Azure Blob Storage or Azure Data Lake Storage linked services with the files relocated to cloud storage. Every connection in every package must be individually assessed for authentication method, network path, and credential storage.
Before migrating a single package, we run every .dtsx file through automated analysis that inventories control flow tasks, data flow transformations, Script Task code, custom components, connection managers, Expression Language usage, and package execution dependencies. Each package gets a complexity score and tier assignment (Tier 1: automated conversion, Tier 2: manual ADF rebuild, Tier 3: Azure-SSIS IR or rewrite). You get a spreadsheet showing every package, its complexity score, recommended migration path, estimated effort in hours, and migration priority based on business criticality.
Microsoft's SSIS Migration Assistant converts simple packages to ADF pipelines automatically. We run every Tier 1 package through the tool, then validate every converted pipeline against the original package output using row-count checks, checksum comparisons, and sample data validation. The assistant handles Execute SQL Tasks, simple data flows with source-destination mapping, basic expression conversion, and straightforward control flow logic. Packages that fail conversion or produce incorrect output get escalated to Tier 2 manual rebuild.
For packages with meaningful business logic, we rebuild the pipeline natively in ADF. SSIS Lookup transformations become ADF Lookup activities or mapping data flow lookup transforms. Conditional Splits become mapping data flow conditional split transforms with translated expressions. Derived Columns become mapping data flow derived column transforms. Slowly Changing Dimension logic gets rebuilt using ADF's SCD transformation in mapping data flows, which actually handles Type 1, Type 2, and Type 6 changes more cleanly than SSIS ever did. Every rebuilt pipeline gets tested against production data and validated against the original SSIS package output before cutover.
For packages that cannot be converted — heavy Script Tasks, custom third-party components, complex package execution chains — we provision an Azure-SSIS Integration Runtime. This is a fully managed cluster of Windows VMs in Azure that runs your existing .dtsx packages with zero code changes. We configure the cluster sizing (Standard_D2_v3 minimum, Standard_D8_v3 for heavy workloads), node count (2-node minimum for production), SSISDB catalog on Azure SQL Database, VNet integration for accessing on-premises resources, and auto-start/stop schedules to minimize cost. Packages run exactly as they did on-premises, and you can trigger them from ADF pipelines alongside native ADF activities.
SSIS Expression Language and ADF expression language are syntactically incompatible. SSIS uses C#-style syntax: (DT_STR, 10, 1252)DATEPART("yyyy", GETDATE()). ADF uses function-based syntax: @formatDateTime(utcnow(), 'yyyy'). We translate every expression manually — variable assignments, derived column formulas, conditional split conditions, precedence constraint expressions, and property expressions. Complex expressions that cannot be represented in ADF's language get moved to Azure Functions or Stored Procedures that the pipeline calls as activities.
SSIS packages are typically scheduled through SQL Server Agent jobs, often with complex multi-step jobs that chain package executions with T-SQL scripts and conditional logic between steps. We migrate these to ADF triggers: Schedule triggers for time-based execution, Tumbling Window triggers for backfill scenarios, Event triggers for file-arrival patterns (replacing SSIS file watchers), and Custom Event triggers via Event Grid for complex orchestration. SQL Agent job step dependencies become ADF pipeline activity dependencies with success, failure, and completion conditions that replicate the original execution flow.
We had 180 SSIS packages running on SQL Server 2016 with end-of-support approaching. FreedomDev categorized every package, converted 70% to native ADF pipelines, moved 30% to Azure-SSIS IR, and had us fully migrated in 10 weeks. Our monthly ETL infrastructure cost dropped from $6,200 to $900 and our nightly batch processing finishes 3 hours earlier.
We connect to your SSISDB catalog (or file system deployment) and extract every package, project, environment, connection manager, and SQL Agent job schedule. Each package gets decompiled to identify all control flow tasks, data flow components, Script Task source code, custom component references, and inter-package execution dependencies. The output is a complete dependency graph showing which packages call which packages, which packages share connection managers, and which SQL Agent jobs orchestrate multi-package workflows. This inventory drives every subsequent migration decision. Typical discovery: companies think they have 80 packages but actually have 150-250 when you include child packages, utility packages, and packages deployed outside the SSISDB catalog to file system paths that nobody remembers.
Every package gets a complexity score from 1-10 based on weighted factors: number of data flow transformations (1 point per 5), Script Task presence (3 points each), custom component usage (4 points each), Expression Language complexity (1-3 points), number of connection managers (1 point per 3), and inter-package dependencies (2 points per chain). Packages scoring 1-3 go to Tier 1 (automated conversion). Packages scoring 4-6 go to Tier 2 (manual ADF rebuild). Packages scoring 7-10 go to Tier 3 (Azure-SSIS IR or rewrite). We present the tiering with effort estimates and let your team adjust priorities based on business criticality, upcoming deadlines, and budget. A typical 200-package environment splits roughly 40% Tier 1, 35% Tier 2, and 25% Tier 3.
Before migrating packages, we stand up the ADF environment: create the Data Factory instance, configure Git integration for pipeline version control (Azure DevOps or GitHub), provision linked services for all target data stores (Azure SQL Database, Azure Blob Storage, Azure Data Lake Storage Gen2, on-premises SQL Server via self-hosted IR), set up Azure Key Vault for credential management, and if Tier 3 packages exist, provision the Azure-SSIS Integration Runtime with appropriate sizing. We configure the self-hosted integration runtime on a VM with network access to any on-premises systems that cloud pipelines need to reach. All infrastructure is provisioned via ARM templates or Bicep for repeatable deployment across dev/staging/production environments.
Migration proceeds in priority order, typically starting with Tier 1 packages to build momentum and validate the process. Tier 1 packages run through the SSIS Migration Assistant, get validated, and move to production. Tier 2 packages get manually rebuilt in ADF with mapping data flows, tested against production data, and validated against original package output. Tier 3 packages get deployed to the Azure-SSIS IR or rewritten with Azure Functions handling Script Task logic. Each batch of migrated packages includes parallel running against the original SSIS packages to verify data accuracy. A 200-package migration typically takes 6-10 weeks of active development with 2-3 engineers working in parallel on different tiers.
Every migrated pipeline runs in parallel with the original SSIS package for a minimum validation period of 1-2 weeks per batch. We compare row counts, checksums, and sample records between old and new outputs. Schedule triggers are activated in ADF only after validation passes. SQL Agent jobs are disabled one at a time as their ADF replacements prove stable. We provide a 30-day hypercare period post-cutover to catch edge cases: month-end processing differences, quarterly batch jobs that did not run during validation, and timezone-related scheduling discrepancies. Once all packages are migrated and validated, we decommission the SSIS infrastructure: disable SQL Agent jobs, drop the SSISDB catalog, and document the mapping from every old package to its ADF pipeline replacement for your team's reference.
| Metric | With FreedomDev | Without |
|---|---|---|
| Execution Model | Serverless pay-per-activity-run (ADF native) | Always-on SQL Server instance with SSIS runtime |
| Monthly Infrastructure Cost (200 packages) | $200–$800/mo (ADF serverless, activity-based) | $3,000–$8,000/mo (SQL Server licenses + Windows Server + storage) |
| Scaling | Auto-scale integration runtimes, parallel pipeline runs | Fixed server capacity, manual scale-up requires downtime |
| Monitoring | ADF Monitor, Azure Monitor, Log Analytics, custom alerts | SQL Agent job history, SSISDB execution reports, custom SSIS logging |
| Version Control | Native Git integration (Azure DevOps / GitHub) | SSISDB catalog with project deployment, no native Git |
| Disaster Recovery | Cross-region ADF replication, geo-redundant linked services | SQL Server AlwaysOn for SSISDB, manual package backup/restore |
| Script Task / Custom Code | Azure Functions, Databricks notebooks, Stored Procedures | Inline C#/VB.NET Script Tasks, custom compiled components |
| Maintenance Overhead | Zero patching (PaaS), Microsoft-managed runtime updates | Monthly Windows updates, SQL Server CUs, SSIS component patches |
Schedule a direct technical consultation with our senior architects.
Make your software work for you. Let's build a sensible solution.