Replacing SSIS with Azure Data Factory: A Practical Migration Playbook for SQL Developers

1. SSIS vs Azure Data Factory: What Actually Changes

Before planning a migration, map the mental model shift.

1.1 Core concepts: old vs new

• SSIS

  • On‑premises runtime (SSIS service / SQL Server)
  • Visual packages in SSDT
  • Control Flow vs Data Flow
  • SSIS Catalog for deployment and logging

• Azure Data Factory (ADF)

  • Cloud‑native service, managed runtime (Integration Runtimes)
  • JSON-based pipelines (authoring in UI, ARM, or code)
  • Activities and datasets instead of tasks and connection managers
  • Azure Monitor / Log Analytics instead of SSIS Catalog reports

1.2 What maps cleanly, what doesn’t

Easy-ish mappings:

• Execute SQL Task → Stored procedure / Lookup / Copy activity
• File loads → Copy activity (file → table)
• Schedules → Triggers (time, event, tumbling window)

Needs redesign:

• Complex Data Flows with many transformations
• Heavy script components (C#) and Script Tasks
• Packages relying on SSIS-specific features (Checkpoints, Sequence Containers, Event Handlers)

You can often keep your SQL logic and refactor the orchestration around it.

2. Migration Strategy: Don’t Lift-and-Shift, Slice-and-Replace

2.1 Decide what to migrate first

Prioritize pipelines based on:

1. Business impact
   • Critical daily loads come first.
2. Complexity
   • Start with medium complexity packages (not trivial, not monsters) to learn the pattern.
3. External dependencies
   • Identify packages that depend on:
     • Local file shares
     • On‑prem SQL
     • Legacy systems

Create a simple inventory:

• Package name
• Frequency (daily, hourly, ad‑hoc)
• Sources/targets
• Main transformations
• Special logic (scripts, custom components)

2.2 Choose a target architecture pattern

Common patterns when moving SSIS to ADF:

• Direct ADF Pipelines + Copy + Data Flows
  • For straightforward ETL, small to medium complexity.
• ADF Orchestration + Stored Procedures
  • Keep most logic in SQL, use ADF to move data and call procs.
• ADF + Databricks / Synapse
  • For heavy transformations, big data, or complex business rules.

For SQL‑heavy teams, start with ADF orchestrating stored procedures. It’s fast to implement and keeps the learning curve manageable.

3. Mapping SSIS Patterns to ADF Components

3.1 Control Flow → Pipelines and Activities

• Sequence Container → Group related activities in one pipeline or use nested pipelines.
• Precedence Constraints → Activity dependencies with success/failure conditions.
• ForEach Loop → ForEach activity.
• Execute Package Task → Execute Pipeline activity.

Example: SSIS pattern

• Load staging
• Validate row counts
• Load dimension
• Load fact

In ADF, this becomes a single pipeline with four activities chained by dependencies.

3.2 Data Flow → Copy Activity + Mapping Data Flows

• Simple extract → load: use Copy activity with minimal mapping.
• Transformations like derived columns, lookups, filters, aggregates: use Mapping Data Flows.

Example mapping:

• SSIS Derived Column → Data Flow Derived Column transformation
• SSIS Lookup → Data Flow Lookup (or pre‑join in SQL)

If performance or cost becomes an issue, consider pushing heavy logic into SQL views or stored procedures.

3.3 Variables, parameters, and configuration

• SSIS Parameters / Configurations → Pipeline parameters and global variables (in Key Vault, config tables, or ADF global parameters).
• Environment variables in SSIS Catalog → Key Vault + ADF global parameters or a metadata table.

Example: pipeline parameter snippet (conceptual JSON):

{
  "name": "LoadCustomer",
  "properties": {
    "parameters": {
      "LoadDate": {
        "type": "String",
        "defaultValue": "@{formatDateTime(utcNow(),'yyyy-MM-dd')}"
      }
    }
  }
}

4. Handling On‑Prem SQL and File Shares

4.1 Integration Runtimes

To reach on‑prem sources, you’ll need a Self‑Hosted Integration Runtime (SHIR).

Key steps:

1. Install SHIR on a server with access to your on‑prem databases and shares.
2. Register it in ADF.
3. Point on‑prem linked services (SQL, file, etc.) to this SHIR.

4.2 Linked services and datasets

• Linked Service ≈ SSIS Connection Manager.
• Dataset ≈ SSIS Source/Destination metadata.

Example: SQL linked service (conceptual snippet):

{
  "name": "LS_OnPremSQL",
  "properties": {
    "type": "SqlServer",
    "typeProperties": {
      "connectionString": "Server=ONPREMSQL;Database=DW;"
    },
    "connectVia": {
      "referenceName": "SelfHostedIR",
      "type": "IntegrationRuntimeReference"
    }
  }
}

5. Re‑implementing Common SSIS Tasks in ADF

5.1 File ingestion

Typical SSIS pattern:

• File System Task to move files
• Data Flow to load CSV → SQL
• Logging row counts

ADF equivalent:

1. Get Metadata activity: check file existence / size.
2. Copy activity: file → staging table.
3. Stored procedure: validate and move into final table.
4. Delete activity or Copy to archive: move processed file.

5.2 Row‑by‑row logic and Script Components

Row‑by‑row C# logic in SSIS often doesn’t translate well.

Options:

• Refactor into set‑based SQL:

UPDATE t
SET    t.Status = CASE 
                    WHEN t.Amount > 10000 THEN 'HIGH'
                    WHEN t.Amount > 0     THEN 'NORMAL'
                    ELSE 'INVALID'
                  END
FROM   dbo.Transactions t
WHERE  t.LoadDate = @LoadDate;

• Use Mapping Data Flow expressions for row transformations.
• For complex rules or ML, orchestrate Databricks or other compute.

5.3 Error handling and retries

SSIS event handlers → ADF patterns:

• Use activity-level retry policies (e.g. retry Copy activity 3 times).
• On failure, route to notification pipeline (email, Teams, etc.).
• Log failures to an error table via stored procedure activity.

Example: pattern for failure branch:

1. Activity A fails.
2. On failure, call stored procedure usp_LogPipelineError with parameters:
   • Pipeline name
   • Activity name
   • Error message
   • Run ID

CREATE PROCEDURE dbo.usp_LogPipelineError
    @PipelineName  sysname,
    @ActivityName  sysname,
    @ErrorMessage  nvarchar(max),
    @RunId         uniqueidentifier
AS
BEGIN
    INSERT INTO dbo.PipelineErrorLog
    (
        PipelineName,
        ActivityName,
        ErrorMessage,
        RunId,
        ErrorTime
    )
    VALUES
    (
        @PipelineName,
        @ActivityName,
        @ErrorMessage,
        @RunId,
        SYSDATETIME()
    );
END;

6. Scheduling, Dependencies, and Incremental Loads

6.1 Replacing SQL Agent schedules

In SSIS, you probably use SQL Agent jobs. In ADF, you use triggers:

• Schedule trigger
  • Cron‑like schedules (hourly, daily, etc.).
• Tumbling window trigger
  • Fixed, non‑overlapping time slices (good for incremental loads).
• Event trigger
  • React to blob/file creation.

For most SSIS migrations, schedule triggers are enough.

6.2 Incremental loads with Watermarks

If your SSIS packages use LastLoadDate parameters, you can replicate that in ADF.

Common pattern:

1. Store LastLoadDate per table in a control table.
2. At pipeline start, lookup watermark.
3. Use it in your source query.
4. After successful load, update watermark.

Example watermark table:

CREATE TABLE dbo.LoadControl
(
    SourceName     sysname PRIMARY KEY,
    LastLoadDate   datetime2(0) NOT NULL
);

Example source query in a stored procedure:

CREATE PROCEDURE dbo.usp_LoadSalesIncremental
AS
BEGIN
    DECLARE @LastLoadDate datetime2(0);

    SELECT @LastLoadDate = LastLoadDate
    FROM   dbo.LoadControl
    WHERE  SourceName = 'Sales';

    INSERT INTO dbo.SalesStaging (OrderID, OrderDate, Amount)
    SELECT  s.OrderID,
            s.OrderDate,
            s.Amount
    FROM    dbo.SalesSource s
    WHERE   s.ModifiedDate > @LastLoadDate;

    UPDATE dbo.LoadControl
    SET    LastLoadDate = SYSDATETIME()
    WHERE  SourceName = 'Sales';
END;

ADF pipeline steps:

1. Lookup activity to read LastLoadDate (optional if logic is inside proc).
2. Stored Procedure activity calling dbo.usp_LoadSalesIncremental.

7. Monitoring, Logging, and Cost Control

7.1 Monitoring runs

Instead of SSIS Catalog reports, you now use:

• ADF Monitor blade for pipeline runs and activity details.
• Azure Monitor / Log Analytics for central logs and queries.

Track at least:

• Run duration per pipeline
• Failure rate per activity
• Data volume moved (for cost awareness)

7.2 Cost-aware design

ADF charges are based on:

• Pipeline orchestration
• Data movement
• Data Flow compute

Practical tips:

• Push simple transformations into SQL instead of Data Flows.
• Group small tasks into fewer pipelines instead of many tiny ones.
• Avoid unnecessary copying between regions or between services.

8. A Minimal, Realistic Migration Plan

Turn this into an executable plan instead of a vague “we should move to ADF”.

1. Inventory SSIS packages
   • Capture frequency, dependencies, complexity.
2. Pick 1–2 pilot packages
   • Medium complexity, low business risk.
3. Set up ADF and SHIR
   • Create ADF, configure Git integration, install SHIR.
4. Implement pilot pipelines
   • Recreate control flow, use Copy + stored procedures where possible.
5. Build shared components
   • Standard error logging proc
   • Standard watermark table
   • Shared linked services
6. Review performance and cost
   • Compare run time vs SSIS; adjust design if needed.
7. Roll out in waves
   • Move related packages together (by subject area or source system).
8. Retire SSIS infrastructure
   • After a stable period with parallel runs and reconciled results.

One Concrete Next Step

Pick a single, non‑critical SSIS package and rewrite it in ADF using Copy activities plus stored procedures, keeping all business logic in SQL; once that runs reliably for a week, use the same pattern to migrate the rest of that subject area instead of redesigning everything from scratch.