Microsoft Fabric

Migrate SSIS Packages to Microsoft Fabric: Retire, Rehost, Rebuild or Redesign

A practical SSIS-to-Microsoft Fabric migration guide: what maps to Fabric Data Factory and what needs redesign, how to inventory and score packages, when to use Invoke SSIS Package (Preview), how to migrate in waves, and how to cut over without breaking production.

By JaviPublished Updated 29 min readIntermediate

On this page

Most teams moving to Microsoft Fabric arrive with an SSIS estate: dozens or hundreds of .dtsx packages, the SQL Agent jobs that run them, configuration files, and a few packages nobody wants to touch. The real question is not “which Fabric component replaces the Data Flow Task?” It is:

I have an existing SSIS estate. What actually maps to Microsoft Fabric, what needs redesign, what can be reused, and how do I migrate without breaking production?

The migration principle. Migrating SSIS to Microsoft Fabric is rarely a package-by-package conversion. Every package bundles several responsibilities: orchestration, ingestion, transformation, state, dependencies, scheduling, error handling and operational controls. The work is to separate them, map each one to the Fabric service that fits it, and then decide, package by package, whether to retire, rehost, rebuild or redesign it.

Two facts frame everything below:

  • Microsoft does not document a converter from .dtsx to Fabric pipelines. Third-party conversion tools exist; their output still needs the assessment and validation described here.
  • Fabric can run some existing packages unchanged, through the Invoke SSIS Package activity. It is in Preview, and its documented limits (no on-premises sources, no private endpoints, no custom or third-party components) rule out many real packages.

This guide assumes you know SSIS well: packages, control flow, data flow, connection managers, parameters and configurations, SQL Agent scheduling. It does not assume you know Fabric Data Factory. Statements about what Fabric does are Microsoft-documented and linked in Sources; the sequencing, scoring and design recommendations are Javi Builds migration guidance, and are written as recommendations.

The short answer

An SSIS package and its SQL Agent job are one deployable unit. In Fabric, the same responsibilities usually land in different items:

  1. SSIS package + Agent jobOne .dtsx carries every responsibility
    • orchestration
      Close matchData Factory pipelineControl flow, dependencies, schedules, retries
    • ingestion
      Close matchCopy activity · Copy jobSource to OneLake or Warehouse with light mapping
    • transformation
      Partial matchT-SQL · notebook · Dataflow Gen2Chosen by logic type and volume, not by component
    • state
      Redesign requiredWatermarks · execution logReplaces checkpoints, restart logic and package log tables
    • errors
      Partial matchFailure paths · alertsReplaces event handlers, operators and Send Mail tasks
    • configuration
      Partial matchConnections · variable librariesReplaces connection managers, configurations and environments
One SSIS package, several Fabric homes. A package plus its Agent job bundles responsibilities that land in different Fabric items. The tag gives the match quality; none of these is a file conversion.

Then decide what happens to each package:

Decision What it means Choose it when Evidence you need
Retire Turn the package off and do not migrate it Nobody consumes its output, it duplicates another load, or its source or target is being decommissioned No downstream consumers in the inventory; the owner signs off
Rehost Keep execution in SSIS for now: Invoke SSIS Package (Preview) inside Fabric, an Azure-SSIS integration runtime in Azure Data Factory, or the current server The package works, a rewrite adds little value yet, or it has a blocker with no Fabric-native replacement today It passes the Preview checks, or it has a documented blocker and a review date
Rebuild Reimplement the same behavior with Fabric-native items The logic is sound, but the package is blocked, fragile or expensive to keep on SSIS Expected outputs to test against
Redesign Change the shape: boundaries, storage, load pattern The structure is the problem: nightly full reloads, staging copies that exist only for SSIS, long package chains A target design that is simpler to run and to prove

Most estates use all four. Many valuable packages also go through a hybrid period, where Fabric takes over orchestration, movement and monitoring while SSIS keeps the logic that is risky to rewrite. Hybrid is the route between rehost and rebuild, not a destination:

  1. 1RehostThe package still runs in SSIS; Fabric schedules and monitors it
  2. move one responsibility
    2HybridFabric copies data and handles alerts; SSIS keeps the risky transformation
  3. replace the rest
    3Rebuild or redesignFabric-native pipeline, T-SQL or notebooks; SSIS runtime retired
The usual route for a valuable, complex package. Each step moves one more responsibility out of SSIS, and each one is small enough to validate and roll back. Retire skips the route entirely.

The responsibility map

Map responsibilities, not components. The Match column uses four labels:

  • Close match: the job exists in Fabric and behaves the way an SSIS developer expects, with small differences.
  • Partial match: same purpose, different scope or behavior. Useful as a starting point, dangerous as an assumption.
  • Redesign required: the job still has to be done, but the SSIS mechanism does not exist. Design it again from the requirement.
  • No direct equivalent: nothing in Fabric provides this. Remove the need for it.
SSIS responsibility Fabric destination or approach Match What to watch
Orchestration (control flow, precedence constraints, containers) Data Factory pipeline: activities, dependency conditions (on success, failure, completion, skip), If Condition, ForEach, Until Close match Expression-based precedence constraints become If Condition activities. An activity with several upstream dependencies waits for all of them
Scheduling (SQL Agent) Pipeline schedules (up to 20 per pipeline), event triggers on files, jobs and workspace events; item schedules on notebooks, Copy jobs and dataflows Partial match There is no shared schedule object that starts many pipelines. Agent operators, proxies and step logic do not come along
Database ingestion (OLE DB or ADO.NET source to destination) Copy activity or Copy job, through an on-premises or virtual network data gateway where needed Close match Authentication often changes. Fabric Warehouse accepts Microsoft Entra ID only
File ingestion (FTP or SFTP, file shares, flat files) Copy activity or Copy job into Lakehouse Files or tables; a notebook when parsing goes beyond what Copy can express Partial match Drive letters and UNC paths mean nothing in Fabric. On-premises shares need a gateway
Transformations (Data Flow components) Warehouse T-SQL for set-based logic, Dataflow Gen2 for Power Query-style shaping, notebooks for large or code-heavy logic Partial match Pick by logic type and volume. Spark is not required for every transformation
Stored procedure execution (Execute SQL Task) Script or Stored procedure activity against a Fabric Warehouse, SQL database in Fabric, or an external SQL Server or Azure SQL connection Close match Fabric Warehouse T-SQL differs: no triggers, no recursive queries, constraints are NOT ENFORCED
Package variables Pipeline variables and parameters; notebook parameters Close match Variables are scoped to the pipeline run; there are no container-scoped variables
Parameters and configurations (.dtsConfig, project parameters, SSISDB environments) Pipeline parameters plus a variable library per environment Partial match Only .dtsConfig is a documented input when you rehost with Invoke SSIS Package
Connection managers Fabric connections (cloud or gateway); Connection Managers overrides for a rehosted package Partial match Credentials live in the connection, not in the package
Logging (log providers, SSISDB reports) Monitor hub and pipeline run history, plus your own execution log Partial match Run history says that a run happened, not what it loaded
Retries Activity retry count and interval; explicit retry loops for whole stages Close match A retry is only safe when the step is idempotent
Checkpoints Idempotent steps, durable state in watermark or control tables, explicit restart logic Redesign required There are no checkpoint files. See Checkpoints
Transactions (TransactionOption, MSDTC) Warehouse transactions inside one engine; staged, idempotent loads across steps No direct equivalent A pipeline cannot wrap several activities in one transaction. See Transactions
Script Task A pipeline expression, notebook, Functions activity (User data functions) or a service outside Fabric, depending on what the script does Redesign required Rewrite the intent, not the C#. See Script Tasks
Execute Package Task Invoke pipeline (child pipeline), or a flatter parent pipeline Partial match Redraw the chain before rebuilding it. See Package chains
Event handlers (OnError, OnTaskFailed) On-failure dependency paths, Fail activity, Office 365 Outlook or Teams activities, alerts from monitoring Partial match No event bubbling: every path is wired explicitly. An Outlook activity wired to several failing activities fires only when all of them fail
Deployment (ISPAC, SSISDB folders) Git integration and deployment pipelines, or fabric-cicd Partial match Lakehouse Files, where rehosted packages live, are not carried by Git

For the tasks and components teams ask about most:

SSIS task or component Likely Fabric approach Notes
Data Flow Task, movement only Copy activity or Copy job Good fit when the flow is source to destination with light mapping
Data Flow Task, heavy transformation Land first, then Warehouse T-SQL, Dataflow Gen2 or a notebook Lookups, derived columns and conversions usually become set-based SQL
OLE DB Destination into a Fabric Warehouse Copy activity, or COPY INTO from staged files OLE DB Destination does not work against Fabric Warehouse
Flat File Source Files in a Lakehouse or ADLS Gen2, read by Copy activity, notebook or Dataflow Gen2 Encodings, delimiters and header rows need explicit settings
File System Task Copy activity (binary copy), Delete activity, notebook file operations Archiving becomes a path convention, not a folder move on a server
Foreach Loop Container ForEach activity over a Get Metadata or Lookup output Check the batch count; a per-file loop is often better as a folder-level copy
Sequence Container Grouped activities, or a child pipeline Child pipelines make reuse explicit
Send Mail Task Office 365 Outlook or Teams activity, or alerting from monitoring User-authenticated connections must be recreated in each workspace
Script Component Set-based T-SQL, or a notebook Row-by-row logic is usually better as set-based logic

Before choosing where transformations run, Microsoft Fabric Pipelines vs Notebooks covers the tool choice and Microsoft Fabric Lakehouse vs Warehouse covers where the data should live. For the wider translation of SQL Server concepts (instance, database, SQL Agent, Query Store), read SQL Server to Microsoft Fabric: What Maps to What?.

Fabric does not make a poor SSIS design good. A package that reloads everything every night, swallows errors and has no restart logic will do exactly the same in Fabric. Migration is the moment to fix those things, or at least to write them down.

Inventory and assessment

Do this before you touch any package. The inventory turns “migrate SSIS” from a vague project into a list of decisions.

For each package, record:

Area What to capture Why it matters
Identity and purpose Name, location (file system, SSISDB folder and project, MSDB), one sentence on what breaks if it stops Packages outside the file system must be exported; purpose drives priority
Owner A named person or team Someone must sign off retirement or cutover
Sources Server, database, file location or API, and where each lives (cloud, on-premises, private network) On-premises and private sources block Invoke SSIS Package today
Destinations Same detail as sources Fabric Warehouse destinations need Entra authentication and COPY INTO
Frequency and trigger Schedule, file arrival, upstream job, manual Decides schedule, event trigger or parent pipeline
Dependencies Packages and jobs that must run before or after; shared staging tables Cutover order and wave grouping
Connection managers Type (OLE DB, ADO.NET, Flat File, Azure Storage…), provider, authentication Provider and authentication changes are common
Parameters, variables and configurations Package and project parameters, .dtsConfig, environment variables, registry, SQL Server configurations, SSISDB environments Become pipeline parameters, variable libraries or property overrides
Execute SQL Tasks Count, and whether they call procedures or run inline SQL Usually a close match; check T-SQL compatibility for Fabric Warehouse
Data flows Count, components, row volumes Decides Copy, T-SQL, Dataflow Gen2 or notebook
Script Tasks and Script Components Language, purpose, referenced assemblies Usually the hardest part to replace
Custom or third-party components Tasks, components, connection managers Block Invoke SSIS Package today
External executables Execute Process Task targets, PowerShell, command-line tools Need a new home; Fabric has no server to run them on
File-system dependencies Local paths, UNC shares, mapped drives, archive folders Need a cloud location such as Lakehouse Files or ADLS Gen2
Checkpoints and restart behavior SaveCheckpoints, checkpoint files, how operators rerun a failed load Need an idempotent redesign
Transactions Containers with TransactionOption = Required, MSDTC use No direct equivalent
Error handlers Event handlers, failure precedence paths, who gets notified Must be reproduced or improved explicitly
Protection level Current setting User-key levels fail outside the author’s profile
Scheduling mechanism SQL Agent job (steps, schedules, operators, proxies), Windows scheduler, another orchestrator See SQL Agent
Execution history Runs, failures, typical and worst-case duration, rows and bytes per run SLA, cost, and the baseline you validate against
Downstream SLA and consumers When data must be ready; reports, extracts and systems that read it Cutover timing, validation scope, and retire candidates

Automate what you can. The .dtsx format is XML, so tasks, data-flow components, connection managers, protection levels, checkpoints, transactions and event handlers can be extracted across the whole estate. This read-only PowerShell script writes one CSV row per package:

# Inventory every .dtsx under a folder: tasks, data-flow components, connections and risk flags.
# Read-only. Packages saved with EncryptAll* protection levels are encrypted and report no tasks.
param([string]$Root = '.', [string]$Out = '.\ssis-inventory.csv')

$ns = @{ DTS = 'www.microsoft.com/SqlServer/Dts' }
function Count-Values($values) { ($values | Group-Object | Sort-Object Count -Descending | ForEach-Object { "$($_.Name)=$($_.Count)" }) -join '; ' }

Get-ChildItem -Path $Root -Filter *.dtsx -Recurse | ForEach-Object {
    [xml]$xml = Get-Content -LiteralPath $_.FullName -Raw
    $pkg = $xml.DocumentElement
    $attr = { param($xpath, $name) Select-Xml -Xml $xml -XPath $xpath -Namespace $ns | ForEach-Object { $_.Node.GetAttribute($name, $ns.DTS) } }

    $tasks = & $attr '//DTS:Executable' 'ExecutableType' | Where-Object { $_ -ne 'Microsoft.Package' }
    $components = Select-Xml -Xml $xml -XPath '//component' | ForEach-Object { $_.Node.GetAttribute('componentClassID') }

    [pscustomobject]@{
        Package            = $_.FullName
        ProtectionLevel    = $pkg.GetAttribute('ProtectionLevel', $ns.DTS)   # empty = 1 (EncryptSensitiveWithUserKey)
        Checkpoints        = $pkg.GetAttribute('SaveCheckpoints', $ns.DTS) -eq 'True'
        RequiredTxScopes   = @(Select-Xml -Xml $xml -XPath "//*[@DTS:TransactionOption='2']" -Namespace $ns).Count
        EventHandlers      = (& $attr '//DTS:EventHandler' 'EventName' | Sort-Object -Unique) -join '; '
        Tasks              = Count-Values $tasks
        NonMicrosoftTasks  = Count-Values ($tasks | Where-Object { $_ -notmatch '^(Microsoft\.|STOCK:)' })
        DataFlowComponents = Count-Values $components
        Connections        = Count-Values (& $attr '//DTS:ConnectionManager' 'CreationName')
    }
} | Export-Csv -Path $Out -NoTypeInformation

Read the output with care. NonMicrosoftTasks lists tasks whose type is not a built-in SSIS task or container: start the custom-component review there. A Script Component and a .NET custom data-flow component both appear as Microsoft.ManagedComponentHost, so open those packages. The script reads the package format used since SQL Server 2012; older formats need a different parser.

For packages deployed to the SSIS Catalog, execution history is already in SSISDB. This query summarizes the last 90 days per package:

SQL query: Inventory and assessmentT-SQL (Fabric Warehouse)
-- Runs, failures and duration per package over the last 90 days (SSIS Catalog only).
SELECT folder_name,
       project_name,
       package_name,
       COUNT(*)                                                  AS runs,
       SUM(CASE WHEN status IN (4, 6) THEN 1 ELSE 0 END)         AS failed_runs,  -- 4 failed, 6 ended unexpectedly
       AVG(DATEDIFF(SECOND, start_time, end_time))               AS avg_seconds,
       MAX(DATEDIFF(SECOND, start_time, end_time))               AS max_seconds,
       MAX(start_time)                                           AS last_run
FROM SSISDB.catalog.executions
WHERE start_time >= DATEADD(DAY, -90, SYSDATETIMEOFFSET())
GROUP BY folder_name, project_name, package_name
ORDER BY runs DESC;
T-SQL (Fabric Warehouse)13 linesRead-only query display. It is not connected to a database.

A package that has not run in 90 days, or whose output nobody can name a consumer for, is a retire candidate before it is a migration candidate. Spend the human time on business purpose, owners, SLAs and downstream consumers, which no script will find.

Score migration complexity

Score each package by its hardest signal, not by the average. One custom component makes a package high complexity, whatever the rest looks like.

Complexity Typical signals Usual decision
Low Copies data or files between reachable systems; simple Execute SQL Tasks; linear control flow; no custom components; supported authentication Rebuild natively, or rehost if it passes every Preview check
Medium Several dependencies; real transformations; parameters and configurations; conditional flows; a few Script Tasks with a clear purpose; on-premises sources Rebuild, often through a short hybrid period
High Custom or third-party components; complex Script Tasks; package-level transactions; tight coupling to file shares or the local server; COM or native dependencies; deep Execute Package chains; complex checkpoints or event handlers Rehost or hybrid first, then redesign; never in the first wave

Combine complexity with business criticality to place each package in a wave. Low-complexity, low-criticality packages come first because they teach the team the platform. High-complexity, high-criticality packages come last, with the most test data.

Rehost: run the packages you keep

Rehost keeps the package logic as it is and changes where it runs or who orchestrates it. There are three ways to do it:

Option Where the package runs When it fits
Invoke SSIS Package (Preview) A Fabric-managed SSIS runtime, from a Fabric pipeline The package passes every Preview check
Azure-SSIS integration runtime Azure Data Factory, invoked from Fabric with the Invoke pipeline activity The package needs on-premises or private networking, or custom components installed through custom setup
Leave it where it is The current SQL Server and SQL Agent A blocker has no replacement yet, or the package will be retired soon; record the reason and a review date

Invoke SSIS Package (Preview)

The workflow, as Microsoft currently documents it:

  1. Upload the package to OneLake. Put the .dtsx, and its .dtsConfig if it has one, into a Lakehouse Files folder such as Files/ssis-packages/, with Upload → Upload files or OneLake file explorer.
  2. Add the activity. In a Fabric pipeline, search the Activities pane for Invoke SSIS Package.
  3. Choose the package. On the Settings tab, select Browse next to Package path.
  4. Add configuration, if any. Select the .dtsConfig in Configuration path (optional).
  5. Supply runtime values. Override connection-manager properties on the Connection Managers tab (Scope, Name, Property, Value), and package properties or variables on the Property Overrides tab, for example \Package.Variables[User::<variable name>].Value. Overrides accept pipeline expressions, parameters and system variables, which is how one package serves several environments or dates.
  6. Enable logging so package-execution logs are written to OneLake; the activity output contains the logging path.
  7. Run or schedule the pipeline, and follow it in the pipeline Output tab or the Monitor hub.

The activity’s General tab has the usual timeout, retry and retry-interval settings.

Preview limits

Preview. The Invoke SSIS Package activity is in preview. Preview features can have limited functionality and change before general availability. Check the current documentation before you plan production work around it.

Microsoft documents these limits for the preview. A package that hits one of them cannot run through the activity today:

  • OneLake only. Packages in the SSIS Catalog (SSISDB) or MSDB must be exported to .dtsx files and uploaded.
  • No on-premises data sources or destinations.
  • No private-network endpoints, such as VNet-injected or private-endpoint resources.
  • No custom or third-party components.
  • Fixed runtime. Each workspace gets 4 vCores for SSIS execution, which cannot be changed during the preview, and the runtime stays up for a fixed 30-minute time-to-live after the last activity finishes.

Look at what that excludes in a typical estate: anything reading an on-premises SQL Server, anything writing to a file share, anything using a vendor connector, anything in a locked-down network. For many organizations that is most of their packages. Inventory first, then decide; do not discover these limits during cutover.

Microsoft’s tutorials show what the activity can reach: SQL database in Fabric (Microsoft Entra service principal with the OLE DB connection manager), ADLS Gen2 surfaced in OneLake through a shortcut, and Fabric Data Warehouse (Microsoft Entra authentication plus COPY INTO). Two details matter for planning:

  • Fabric Data Warehouse accepts only Microsoft Entra ID authentication. A package that connects with SQL or Windows authentication needs its connection managers changed.
  • OLE DB Destination does not work against Fabric Data Warehouse. Microsoft’s guidance is to stage data in ADLS Gen2 and load it with COPY INTO from an Execute SQL Task. A package built around an OLE DB Destination into the warehouse needs real changes before it can be “lifted”.

Project-deployment features (project parameters, project connection managers, SSISDB environments) are not described in the activity documentation. Test them with a real package before you plan around them.

Integration runtimes and gateways

If your packages run today on an Azure-SSIS integration runtime, or your ADF pipelines use self-hosted or VNet-enabled integration runtimes, those concepts do not carry over one-to-one. Microsoft’s ADF-to-Fabric upgrade guidance maps them like this:

Azure Data Factory Fabric Notes
Azure integration runtime (public network) Nothing to move Fabric runs cloud compute on your capacity
Self-hosted integration runtime On-premises data gateway Recreate as gateways; plan sizing and failover
VNet-enabled Azure integration runtime Virtual network data gateway Deployed into your own virtual network
Azure-SSIS integration runtime Not available in Fabric Fabric’s SSIS option is the Invoke SSIS Package activity, with its own managed runtime

Gateways serve specific Fabric workloads. Microsoft lists the on-premises data gateway for pipelines, Copy job, Dataflow Gen2 and mirroring, among others, with support set per connector. The Invoke SSIS Package documentation does not describe gateway support and says the activity cannot reach on-premises or private-network systems. Do not assume that installing a gateway lets a rehosted package read your on-premises SQL Server.

Microsoft’s pages are at different stages. The ADF-vs-Fabric comparison still lists Azure-SSIS integration runtimes as “to be determined”, and the upgrade guidance suggests running SSIS packages in ADF and calling them from Fabric. The feature article describes the preview activity. Treat the feature article as the authority for what the activity does, and the upgrade guidance as the description of the ADF bridge.

For an on-premises package, the options are therefore: rebuild the movement in Fabric (Copy activity or Copy job through a gateway, then transform in Fabric), land the data in a cloud location the package can read and rehost only the transformation, or keep the package on an Azure-SSIS integration runtime and orchestrate it with the Invoke pipeline activity.

Sensitive values and protection levels

Protection levels decide whether a package can run outside the machine and user that saved it. Check every package before you upload it:

Protection level What happens when the package moves to Fabric
DontSaveSensitive Sensitive values are not stored. Supply them at run time on the Connection Managers tab; Microsoft calls this case out explicitly.
EncryptSensitiveWithPassword / EncryptAllWithPassword The package needs its password. Microsoft’s SSIS tutorials show an optional Encryption password setting; it is not on the main activity page, so confirm it in your tenant.
EncryptSensitiveWithUserKey / EncryptAllWithUserKey Encrypted with the key of the Windows user who saved it. Another user cannot decrypt the sensitive values, and execution fails. Re-save with a different level first.
ServerStorage Used by SSISDB and MSDB. A project exported from the catalog to the file system becomes EncryptSensitiveWithUserKey, so exported packages need re-saving too.

DontSaveSensitive with runtime overrides is the cleanest model: the file contains no secrets, and credentials are managed in one place. Never upload a package with plain-text credentials to a shared Lakehouse; anyone with access to the files can read them.

Cost model

Microsoft bills the activity on SSIS uptime in the workspace, on the “SSIS in Fabric” meter, at 1.35 CU hours per vCore. Uptime starts when the first Invoke SSIS Package activity in the workspace starts, continues while at least one is running, and lasts for the fixed 30-minute time-to-live after the last one finishes. Pipeline orchestration and OneLake storage and transactions are charged on their own meters. What that means for design:

  • Scattered short packages cost more than they look. A 2-minute package that runs every hour keeps the runtime up for about 32 minutes of each hour.
  • Grouping runs helps. Packages that start close together share one uptime window.
  • SSIS uptime draws on the same capacity as your notebooks, warehouses and reports. Fabric Capacity and Compute Usage explains how that consumption shows up, and the Fabric Capacity Planner helps you judge headroom.

Check the current pricing page before you estimate; preview rates can change.

Hybrid: modernize around SSIS

The hybrid strategy keeps the parts of SSIS that are expensive or risky to rewrite and moves everything around them into Fabric. Typical shapes:

  • Fabric orchestrates the package. A pipeline runs it, then the downstream steps: a Warehouse procedure, a semantic model refresh, a notification.
  • Fabric moves the data, SSIS transforms it. A Copy activity or Copy job lands source data in the cloud, and the package only does the transformation it was good at.
  • Fabric owns monitoring. Pipeline runs, the Monitor hub and a shared execution log replace the package’s logging tables as the operational source of truth.
  • Strangler pattern. Each release moves one more responsibility out of the package (first the extraction, then a lookup, then the load) until the package is small enough to retire.

Why this reduces risk: you change one thing at a time, the risky logic keeps running in the engine it was tested in, Fabric scheduling and monitoring arrive early, and a rollback moves one responsibility back instead of restoring a whole ETL estate. The cost is a period with two technologies to operate. Give every hybrid package an owner and a target state, and track how much SSIS logic is left.

Rebuild and redesign

Rebuild keeps the behavior and changes the implementation: the same inputs, outputs and business rules, built from pipelines, Copy activity or Copy job, Dataflow Gen2, notebooks and Warehouse T-SQL, as in the responsibility map. Rebuild when the package is heavily scripted, depends on unsupported components, relies on server-local behavior, fails often, or is blocked by the Preview limits and the ADF bridge is not worth its operational cost.

Redesign changes the shape. It is the right call when the package structure exists because of SSIS, not because of the business:

  • Full nightly reloads become incremental loads with watermarks and MERGE.
  • Staging databases and copy-of-a-copy tables collapse into one landing area in OneLake that other workloads can reuse.
  • Per-file loops become folder-level copies and set-based processing over everything that arrived.
  • Package boundaries follow data products and SLAs instead of the original developer’s project layout.
  • Error handling and logging become one shared execution log instead of a log table per package.

Redesign costs more up front and needs the strongest test evidence, because outputs may change shape. Do not redesign and change business rules in the same release: prove the new design reproduces the old numbers first.

Worked example: a daily partner file

A sanitized, generic package of the kind most estates contain. Every night, a SQL Agent job at 06:00 runs LoadPartnerSettlements.dtsx:

  1. A third-party SFTP task downloads the day’s file to a UNC share, \\<file-share>\inbound\.
  2. A Script Task checks that the file exists, the header is correct and the trailer row count matches.
  3. Inside a Sequence Container with TransactionOption = Required (MSDTC), an Execute SQL Task truncates stg.Settlement, and a Data Flow (Flat File Source → Derived Column → Data Conversion → Lookup → OLE DB Destination) loads it.
  4. An Execute SQL Task runs etl.usp_LoadSettlement, which merges staging into dbo.Settlement and recalculates rpt.DailySettlement on an on-premises SQL Server.
  5. A File System Task moves the file to an archive folder.
  6. A Send Mail Task tells the finance team the totals are ready; an OnError event handler emails support.
  7. Checkpoints are enabled, so an operator can rerun from the failed task.

Why it cannot simply be rehosted

Through Invoke SSIS Package (Preview), it fails three checks: a third-party component, a UNC share, and an on-premises destination. It could be rehosted on an Azure-SSIS integration runtime (custom setup for the SFTP component, networking to the share and the server) and orchestrated from Fabric. That is a valid bridge, but every one of those dependencies would still exist afterwards.

The Fabric design

  • Legacy SSISOne package, one Agent job
    1. SQL Agent at 06:00
    2. Third-party taskSFTP to UNC share
    3. Script Task validation
    4. MSDTC transactionTruncate + data flow
    5. usp_LoadSettlement
    6. Archive, Send Mail
    7. EliminatedCheckpoint restart
  • Microsoft FabricOne pipeline, state in tables
    1. Schedule or file trigger
    2. ChangedCopy activity from SFTPLands in Lakehouse Files under a dated path
    3. ValidateGet Metadata plus a Lookup or notebook check
    4. Load stagingCopy activity or COPY INTO the Warehouse
    5. Must be idempotentIdempotent load procedureReplaces one business date in one transaction
    6. Execution log rowFile, rows, totals, status
    7. Alert on failure or lateness
The partner-file load, before and after. Not a task-for-task conversion. Read each column top to bottom; the highlighted steps are where the design changed, and the Fabric side has no checkpoint or MSDTC step at all.
Category In this example
Stayed the same conceptually Download, validate, stage, calculate, notify. The business rules in usp_LoadSettlement, ported to Warehouse T-SQL and tested against the old outputs
Changed SFTP download (Copy activity, built-in SFTP connector); landing location (Lakehouse Files, dated path); validation (Get Metadata plus a check, instead of C#); destination (Fabric Warehouse, Entra authentication); notifications (Outlook or Teams activity, plus monitoring alerts)
Eliminated The third-party SFTP component, the UNC share, the MSDTC transaction, checkpoint files, the archive move (the dated landing path is the archive)
Must become idempotent The load procedure, so that rerunning a business date gives the same result; the staging load, so that a retry does not duplicate rows; the execution log, keyed by run and file

The idempotent load replaces both the MSDTC transaction and the checkpoint. All its writes happen inside one engine, so a Warehouse transaction is enough, and a rerun replaces the business date instead of appending to it:

SQL query: The Fabric designT-SQL (Fabric Warehouse)
CREATE PROCEDURE etl.load_settlement
    @business_date date,
    @source_file   varchar(200)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        DELETE FROM dbo.settlement WHERE business_date = @business_date;

        INSERT INTO dbo.settlement (business_date, partner_id, amount, source_file)
        SELECT business_date, partner_id, amount, @source_file
        FROM stg.settlement
        WHERE business_date = @business_date;

        DELETE FROM rpt.daily_settlement WHERE business_date = @business_date;

        INSERT INTO rpt.daily_settlement (business_date, partner_id, total_amount, row_count)
        SELECT business_date, partner_id, SUM(amount), COUNT(*)
        FROM dbo.settlement
        WHERE business_date = @business_date
        GROUP BY business_date, partner_id;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
T-SQL (Fabric Warehouse)30 linesRead-only query display. It is not connected to a database.

The hybrid route for this package moves the safest responsibilities first: the SFTP download and the alerts into the pipeline, then the validation, then the load. Each step is one release with its own parallel run.

How the options compare:

Invoke SSIS Package Azure-SSIS IR bridge Fabric-native
Runs today No: three Preview blockers Yes, with custom setup and networking Yes
Rewrite effort — Low Medium to high
Platforms to operate — Fabric + ADF + SSIS Fabric only
Restart model — Checkpoints, as before Idempotent by business date
Data reusable in OneLake — No Yes: landed files and Warehouse tables
Long-term fit — Transitional Target state

The hardest migration areas

These are where SSIS migrations overrun. Find them in the inventory, not during cutover.

Script Tasks

“Replace every Script Task with a notebook” is the wrong rule. Classify each script by what it does, then choose:

The script… Usual replacement
Sets variables, builds file names or dates A pipeline expression or Set Variable activity
Checks that a file exists, its size or timestamp Get Metadata activity, If Condition
Validates file contents or parses an unusual format A notebook, or Dataflow Gen2 for tabular shapes
Calls a REST API Web activity, Copy activity with the REST connector, or a notebook
Contains business logic in C# Warehouse T-SQL or a notebook, rewritten from the requirement and tested
Calls a COM object, a local executable or the Windows API A service outside Fabric (for example Azure Functions), or a redesign that removes the dependency
Small reusable logic invoked from pipelines User data functions, through the Functions activity

Rewrite the intent, not the C#. Many Script Tasks exist because SSIS lacked a built-in way to do something a pipeline activity now does.

Custom and third-party components

Third-party adapters (SFTP, SharePoint, REST, vendor databases), custom data-flow components, custom connection managers and anything that installs native drivers on the SSIS server all block Invoke SSIS Package today. For each one, check whether a Fabric connector covers the same source; if not, the options are an Azure-SSIS integration runtime with custom setup, a notebook with a library, or a service outside Fabric. Check licensing too: a vendor component licensed per server does not follow the package to a new runtime.

Transactions

SSIS transactions with TransactionOption = Required enlist every task in a container into one MSDTC transaction, even across connections. Fabric pipelines have nothing similar: each activity commits on its own, and a failure halfway through a pipeline leaves the earlier activities’ work in place.

Fabric Warehouse supports multi-statement transactions, under snapshot isolation, inside the warehouse. So the redesign is: put the writes that must be atomic into one procedure in one engine (as in the example above), and make every step across engines idempotent, so that a failed run can be repeated instead of rolled back. Warehouse DML locks the whole table, and concurrent writers to the same table can fail with update conflicts, so design loads that write one table not to overlap.

Checkpoints

Checkpoint files let SSIS restart at the failed task. Fabric has no checkpoint mechanism, and recreating one with flags and files is fragile. Prefer:

  • Idempotent steps that can always be rerun for the same window or file.
  • Durable state in a control or watermark table, advanced only after the load succeeds.
  • Explicit restart logic: the pipeline reads state and skips the windows already loaded.

Incremental Loads in Microsoft Fabric covers safe watermark advancement and reruns in detail.

File-system dependencies

Local paths, UNC shares, mapped drives and archive folders assume a server that does not exist in Fabric. Files land in Lakehouse Files or ADLS Gen2, are reached through a gateway when they must stay on-premises, and are archived by path convention (landing/partner/2026/11/08/) rather than moved. Check what else reads those shares: another application, a person, a scheduled export. Moving the files can break consumers outside the SSIS estate.

Package chains

Large Execute Package Task trees usually record how packages were built over the years, not how the data should flow. Draw the chain, find the real dependencies (which step needs which output), and rebuild it as a short parent pipeline with a few child pipelines, or as independent pipelines started by schedules and events. Recreating a twenty-level tree of child pipelines keeps the old complexity and adds pipeline-run overhead.

SQL Agent

Most SSIS estates are also SQL Agent estates, and the Agent job often carries behavior that nobody wrote down. For every job that runs a package, inventory:

  • Job steps: packages are often one step among T-SQL steps, PowerShell steps and calls to other jobs.
  • Schedules: some jobs have several schedules; some schedules run several jobs.
  • Dependencies: jobs that start other jobs or must run after another finishes.
  • Notifications: operators, email on failure, alerts on specific errors.
  • Retry and flow logic: retry attempts and intervals, “on failure go to step N”.
  • Execution context: proxies and credentials the step runs under.

A pipeline schedule replaces the orchestration role of an Agent job, not every Agent behavior. Retries become activity retry settings, step flow becomes dependency conditions, notifications become activities or alerts, and job chains become pipelines calling pipelines. A pipeline can have up to 20 schedules and can also be started by file, job or workspace events. There is no single schedule object that starts many pipelines, so a schedule shared by several Agent jobs becomes several pipeline schedules or one parent pipeline that invokes the others.

Nothing migrates automatically. Disable an Agent job only when everything it did is accounted for, including the steps that are not SSIS.

Migration waves

Migrate the estate in waves, ordered so that each wave teaches the team something before the next one depends on it. The order is a recommendation; the gates are not optional.

  1. Wave 0Inventory and telemetryInventory, complexity scores, history baselines, retire list, execution log design
  2. gate
    Wave 1Copy and orchestrationLow-complexity copies, simple schedules; proves connections, gateways and monitoring
  3. gate
    Wave 2SQL-heavy packagesExecute SQL and stored-procedure ETL; proves Warehouse T-SQL compatibility
  4. gate
    Wave 3Transformation-heavy packagesData flows rebuilt in T-SQL, Dataflow Gen2 or notebooks; proves reconciliation
  5. gate
    Wave 4Scripts and custom componentsScript Tasks and third-party components, often after a hybrid period
  6. gate
    Wave 5Complex operational dependenciesDeep chains, shared staging, tight SLAs, month-end and cross-team consumers
Migration waves, with a gate between each. Each wave runs the same build, validate, cut over and stabilize cycle. A wave starts only when the previous one has passed its gate.
Gate Passes when
After wave 0 Every package has an owner, a decision (retire, rehost, rebuild, redesign) and a complexity score; the retire list is signed off; the execution log and alerting exist in a development workspace
After wave 1 Connections, gateways and authentication work in every environment; failures alert a named person; the first cutover and rollback were rehearsed
After wave 2 Procedures ported to Fabric Warehouse produce reconciled outputs; T-SQL incompatibilities are listed with their fixes
After wave 3 Reconciliation queries pass for several business cycles; duration and capacity consumption are within budget
After wave 4 Every custom component and Script Task has a replacement or a documented rehost decision with a review date
Before decommissioning No package or Agent job is still needed; the rollback window for the last wave has closed

Start wave 1 with one pilot package: low risk but representative, with a real source, a real destination and a real schedule. Run it through the whole cycle, including cutover. The pilot is where you find the actual blockers: authentication, networking, protection levels, logging gaps.

Inside every wave, each package goes through the same cycle: build in a development workspace connected to Git, validate with parallel runs, cut over, stabilize through several business cycles (including month-end if it matters), and only then retire the old path.

Validation and production cutover

“The package succeeded” is not proof that the migration succeeded. A pipeline can succeed and load the wrong rows. Validate the data and the failure behavior, not the run status.

What to compare during parallel runs

Run the legacy and Fabric versions against the same inputs, the Fabric version writing to a separate target, for an agreed number of runs that includes the hardest days (month-end, large files, late files).

Check How
Row counts Per table and per load window, legacy vs Fabric
Business totals Sums of amounts, quantities and other measures by day, customer, product; signed off by the business owner
Key reconciliation Distinct business keys, and keys present on one side only
Checksums A hash of the business columns per row or per partition, where the volume allows; compare sets, not just counts
Nulls, duplicates, types Nulls vs empty strings, duplicate handling, decimal scale, datetime precision, string length and collation
File outputs Number, names, sizes, encodings, line endings
Late-arriving data Rows that arrive after the watermark moved
Duration and capacity Against the SLA on the slowest days; CU consumption, including SSIS uptime for rehosted packages
Downstream reports Report owners confirm the numbers, not only the engineers

Keep the comparison queries. They become regression tests for the next change.

Failure and rerun tests

Test the unhappy paths before cutover, because production will:

  • Rerun the same window twice. The result must not change.
  • Kill a run halfway and restart it. No duplicates, no gaps.
  • Retry a failed activity through its retry setting. The retried step must be idempotent.
  • Feed a bad file: a missing file, a wrong header, a truncated trailer. The pipeline must fail clearly and alert someone.
  • Break a dependency: stop the gateway, expire a credential. The failure must be visible in monitoring, not only in run history.

Go/no-go and rollback

Decide before the cutover, in writing:

Question Answer before cutover
Who decides go, no-go and rollback? A named owner for the package and a named engineer on call for the first runs
What triggers a rollback? For example: a reconciliation failure on business totals, an SLA miss, or a failure with no fix inside the business window
How long is the rollback window? The number of runs or business cycles during which the old path is kept ready
What does rollback mean? Disable the Fabric schedule, re-enable the Agent job, and repair or reload the affected window in the target
Who supports it afterwards? The team that owns the Fabric pipelines, with access to monitoring and the runbook

Cutover steps

  1. Freeze package changes during parallel runs.
  2. Document the current schedule: times, dependencies, notifications, the Agent job definition.
  3. Run in parallel and compare with the checks above until the results match, or every remaining difference is explained and accepted by the owner.
  4. Disable the old scheduler for this package (the Agent job or schedule, not the package itself).
  5. Enable the Fabric schedule against the real target.
  6. Watch the first runs closely, with a named person on call.
  7. Keep the rollback path for the agreed window: the old job is disabled, not deleted; the old target and package still exist.
  8. Retire the old infrastructure later, after the stabilization period, never on cutover day.

Monitoring after cutover

Moving orchestration without moving monitoring is a common way to lose visibility. Fabric gives you pipeline runs and the Monitor hub (status, duration and errors per activity) and, for rehosted packages, OneLake SSIS logs. Add:

  • An execution log with one row per run and step: run ID, package or pipeline, window or file, rows read, written and rejected, duration, status, failure reason, retry count. Designing an Execution Log for Data Pipelines covers the design.
  • SLA checks: did the data arrive when it was needed, not only did the run succeed.
  • Reconciliation for critical loads after every run, not only during migration.
  • Alerts on failure and on lateness, to a person or channel that is watched.

Monitoring Fabric Pipelines and Notebooks covers the Fabric side, and the pipeline debugging cheat sheet and Debugging Fabric Notebooks and Pipelines help with the first failures. If migrated loads slow other workloads down, the capacity saturation runbook is the procedure to follow.

CI/CD and source control

SSIS projects usually lived in source control as Visual Studio projects, while deployment happened by hand. Fabric changes where the artifacts live:

  • Packages are files. Keep the SSIS project, every .dtsx and .dtsConfig, in Git as the source of truth.
  • Lakehouse Files are not in Git. Fabric Git integration and deployment pipelines track Lakehouse metadata, not the contents of Files. Upload rehosted packages to each environment’s Lakehouse as a scripted, repeatable release step.
  • Pipelines are Fabric items. Pipelines, notebooks and Warehouse objects connect to Git and move across development, test and production workspaces. Git integration in Microsoft Fabric and CI/CD with fabric-cicd cover the setup.
  • Check environment references after deployment. Use parameters and variable libraries for environment differences, and verify connections, package paths and Lakehouse references after each promotion.
  • No production-only fixes. Never fix a package by editing the copy in the production Lakehouse; the next release overwrites it.

Migration anti-patterns

  • Converting package for package. You reproduce the SSIS structure, including the parts that existed only because of SSIS.
  • One Fabric pipeline per SSIS package, without reassessing boundaries. Package boundaries reflect history, not data flow.
  • Moving Script Task code blindly. Classify what each script does; many need no code at all.
  • Ignoring restart behavior. Without checkpoints or idempotency, the first failed run duplicates or loses data.
  • Ignoring operational logging. Nobody knows where to look on the first failure.
  • Testing only successful runs. Production fails; reruns, retries and bad files must be tested.
  • Assuming Spark is required for every transformation, or using notebooks for everything. Set-based logic is often simpler and cheaper in Warehouse T-SQL; light shaping fits Dataflow Gen2.
  • Preserving obsolete staging layers only because SSIS used them.
  • Assuming Invoke SSIS Package or a gateway fixes on-premises connectivity. The preview cannot reach on-premises or private-network systems.
  • Ignoring protection levels and .dtsConfig files. The package fails to decrypt, or runs against the wrong server.
  • Deleting the old job on cutover day. There is no rollback.
  • Rewriting everything at once. The risky packages get rushed at the end.

Recommendation

Start with the inventory, not the tool. Score every package, retire what nobody needs, pilot one, and let each remaining package earn its decision:

  • Retire packages with no consumer, with the owner’s sign-off.
  • Rehost packages that work and pass the Preview checks, or that have a blocker with no replacement yet, and put them on the modernization backlog with a review date.
  • Rebuild packages whose logic is sound, as Fabric pipelines, Warehouse T-SQL and notebooks, often after a short hybrid period.
  • Redesign packages whose structure is the problem, around OneLake, incremental loads, idempotent steps and one execution log.

Treat the Invoke SSIS Package activity as what it is today: a useful Preview way to bring standard, cloud-reachable packages under Fabric orchestration. It is a bridge into the platform, not the destination.

Sources

Microsoft-documented behavior in this article comes from these Microsoft Learn pages:

The decision framework, complexity scoring, wave order, example design, gates and anti-patterns are Javi Builds migration guidance, not Microsoft documentation. The inventory script was tested against sample packages in the SQL Server 2012+ format, not against a production estate.

Fabric behavior checked against Microsoft Learn in November 2026. The Invoke SSIS Package activity is in Preview; recheck its limits before planning production work.

Tags

  • Microsoft Fabric
  • SSIS
  • SQL Server
  • Data Pipelines
  • Data Architecture