SSIS to Azure Data Factory Migration: Package-by-Package Migration Guide

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.