# SSIS to Azure Data Factory Migration: Package-by-Package Migration Guide 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. S... ## SSIS to Azure Data Factory Migration: Package-by-Package Migration Guide 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. --- ## Our Process 1. **SSIS Inventory & Dependency Mapping (1–2 Weeks)** — 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. 2. **Complexity Scoring & Migration Path Assignment (1 Week)** — 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. 3. **Azure Infrastructure Provisioning (1 Week)** — 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. 4. **Package Migration Execution (3–10 Weeks)** — 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. 5. **Validation, Cutover & Decommission (2–4 Weeks)** — 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. --- ## Frequently Asked Questions ### Can we keep running some SSIS packages unchanged in Azure instead of converting everything to ADF pipelines? Yes. Azure-SSIS Integration Runtime is a fully managed cluster of Windows virtual machines inside Azure Data Factory that executes your existing .dtsx packages with zero code changes. You deploy your packages to an SSISDB catalog hosted on Azure SQL Database or Azure SQL Managed Instance, and the IR runs them exactly as your on-premises SQL Server did. The catch is cost: Azure-SSIS IR bills by the hour for every node in the cluster, regardless of whether packages are actively executing. A Standard_D4_v3 two-node cluster costs roughly $1,200-1,600/month running 24/7. You can configure auto-start and auto-stop schedules to run the cluster only during your batch processing windows (for example, 10 PM to 6 AM), which cuts the cost by 60-70%. We recommend Azure-SSIS IR specifically for Tier 3 packages: those with heavy Script Tasks, custom third-party components, or complex package execution chains that would cost more to rewrite than to host on IR. For most clients, 20-30% of packages end up on Azure-SSIS IR while the rest convert to native ADF pipelines. ### What happens to our SSIS Script Tasks during migration? ADF does not have an equivalent. Script Tasks are the most common migration blocker because ADF has no inline code execution equivalent. Each Script Task needs individual assessment based on what the code actually does. Script Tasks that perform data transformation logic (string manipulation, custom parsing, mathematical calculations) typically get migrated to ADF mapping data flow derived column transforms or, for complex logic, to Azure Functions that the pipeline invokes as a Web Activity. Script Tasks that call external APIs or web services become ADF Web Activities or Azure Function activities. Script Tasks that perform file system operations (creating directories, moving files, renaming files) become ADF pipeline activities (Copy, Delete, Get Metadata) or Azure Function calls. Script Tasks that interact with non-standard data sources (COM objects, custom DLLs, proprietary protocols) either remain on Azure-SSIS IR or get rewritten as Azure Functions with the relevant SDK packages installed. We extract the source code from every Script Task during the assessment phase, classify the logic, and determine the migration path before committing to a timeline. Roughly 60% of Script Tasks we encounter contain logic simple enough to express in ADF's native activities or mapping data flow expressions. The other 40% require Azure Functions or stay on the IR. ### How do you handle SSIS packages that connect to on-premises databases after migration to ADF? ADF connects to on-premises data sources through a self-hosted integration runtime, which is a lightweight agent installed on a Windows machine inside your network. The agent establishes an outbound HTTPS connection to ADF (no inbound firewall rules required) and executes data movement and dispatch activities on behalf of ADF pipelines. You install the self-hosted IR on an existing server or a small dedicated VM that has network access to your on-premises databases, file shares, and other systems. For SQL Server specifically, the self-hosted IR supports both SQL Authentication and Windows Authentication. For Windows Auth, the IR machine must be domain-joined and the service account needs appropriate database permissions. We typically configure two self-hosted IR nodes for high availability so that pipeline execution continues if one node goes offline for maintenance. The self-hosted IR is required for any ADF pipeline that accesses on-premises resources, and it has no additional licensing cost beyond the Windows VM it runs on. For organizations with Azure VPN Gateway or ExpressRoute already configured, we can alternatively use the Azure Integration Runtime with private endpoints to reach on-premises systems through the VPN tunnel, avoiding the need for a self-hosted IR agent entirely. ### What does the SSIS Migration Assistant actually convert, and what does it miss? The SSIS Migration Assistant (available as an Azure Data Factory tool) converts SSIS control flow and data flow into ADF pipeline JSON. It handles Execute SQL Tasks, simple data flow transformations (OLE DB Source, Flat File Source, OLE DB Destination, derived columns with basic expressions, row count transforms), For Each Loop Containers, Sequence Containers, basic precedence constraints, and package parameters. What it explicitly does not handle: Script Tasks (C# and VB.NET code is skipped entirely), custom data flow components (third-party or custom-compiled transforms), complex SSIS Expression Language formulas (the tool converts simple expressions but fails on nested functions, type casts, and conditional operators), Send Mail Tasks, WMI tasks, Analysis Services processing tasks, message queue tasks, and package configurations that reference environment variables or registry entries. Event handlers are also not converted. In practice, the Migration Assistant cleanly converts about 30-50% of packages in a typical enterprise SSIS environment. The rest either fail conversion outright or convert with warnings that require manual intervention. We run every converted pipeline through validation testing because the assistant sometimes produces pipelines that execute without errors but produce incorrect output due to expression conversion issues or data type mapping differences between SSIS and ADF. ### How much does SSIS to ADF migration cost and how long does it take? Cost and timeline depend entirely on the number of packages, their complexity distribution, and how many require Azure-SSIS IR versus native ADF conversion. For a small environment (20-50 packages, mostly Tier 1 and Tier 2), expect $30,000-$60,000 and 4-8 weeks. For a mid-size environment (100-200 packages with a mix of all three tiers), expect $80,000-$150,000 and 8-14 weeks. For large enterprise environments (300+ packages with heavy Tier 3 presence, multiple SSIS servers, and complex SQL Agent job orchestration), expect $150,000-$300,000 and 3-6 months. These ranges include discovery, assessment, tiering, infrastructure provisioning, migration execution, validation, parallel running, and cutover support. They do not include Azure consumption costs, which vary based on your data volumes and pipeline execution frequency. The assessment phase (Steps 1-2) is fixed-cost at $5,000-$10,000 regardless of environment size, and we recommend starting there before committing to a full migration budget. The assessment deliverable gives you the package inventory, complexity tiering, migration path recommendations, effort estimates, and Azure infrastructure cost projections so you can make an informed decision. ### What is the difference between ADF mapping data flows and ADF data flows, and which one replaces SSIS data flows? ADF has two types of data flows: mapping data flows and wrangling data flows (now called Power Query data flows). Mapping data flows are the direct replacement for SSIS data flows. They provide a visual transformation designer similar to the SSIS data flow designer, with source transforms, sink transforms, and transformation nodes in between: derived columns, aggregate, filter, join, lookup, conditional split, union, pivot, unpivot, sort, surrogate key, slowly changing dimension, and more. Mapping data flows execute on auto-scaled Apache Spark clusters managed by ADF, which means they can process large data volumes (millions of rows) with parallel execution and partitioning that SSIS was never designed to handle. The Spark execution engine introduces some behavioral differences: data types have different precision characteristics, sort order is not guaranteed through transformations the same way SSIS buffer-based processing guarantees it, and null handling follows Spark semantics rather than SSIS semantics. We test all data type edge cases during migration to catch these differences. Wrangling data flows (Power Query) are a different feature designed for self-service data prep by analysts and do not replace SSIS data flows. They use the Power Query M language and are intended for ad-hoc data exploration, not production ETL pipelines. When we say we migrate SSIS data flows to ADF, we always mean mapping data flows. ### Can ADF handle the same scheduling complexity as SQL Server Agent jobs that orchestrate our SSIS packages? ADF triggers replicate and in many cases exceed SQL Server Agent scheduling capabilities. Schedule triggers handle time-based recurring execution (every hour, daily at 2 AM, every Monday and Thursday, first of the month). Tumbling window triggers handle fixed-size, non-overlapping time intervals with built-in dependency chaining and backfill support, which is something SQL Agent cannot do natively. Event-based triggers fire when files arrive in Azure Blob Storage or Data Lake Storage, replacing SSIS file watcher patterns and WMI event subscriptions. Custom event triggers integrate with Azure Event Grid for complex event-driven orchestration across multiple Azure services. The main gap is multi-step job logic. SQL Server Agent jobs can chain SSIS package execution with T-SQL scripts, PowerShell steps, and CmdExec steps, with conditional logic between steps based on success or failure. In ADF, this translates to pipeline activities with dependency conditions (succeeded, failed, completed, skipped). Execute Pipeline activities call child pipelines (replacing Execute Package Tasks), Stored Procedure activities replace T-SQL job steps, and Azure Function or Web activities replace PowerShell and CmdExec steps. We rebuild every SQL Agent job as an ADF master pipeline that orchestrates the same execution sequence with the same conditional logic, then validate the execution flow against the original job step history. --- **Canonical URL**: https://freedomdev.com/solutions/ssis-to-azure-data-factory _Last updated: 2026-05-12_