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.
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
.dtsxto 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:
- SSIS package + Agent jobOne .dtsx carries every responsibility
- orchestrationClose matchData Factory pipelineControl flow, dependencies, schedules, retries
- ingestionClose matchCopy activity · Copy jobSource to OneLake or Warehouse with light mapping
- transformationPartial matchT-SQL · notebook · Dataflow Gen2Chosen by logic type and volume, not by component
- stateRedesign requiredWatermarks · execution logReplaces checkpoints, restart logic and package log tables
- errorsPartial matchFailure paths · alertsReplaces event handlers, operators and Send Mail tasks
- configurationPartial matchConnections · variable librariesReplaces connection managers, configurations and environments
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:
- 1RehostThe package still runs in SSIS; Fabric schedules and monitors it
- move one responsibility2HybridFabric copies data and handles alerts; SSIS keeps the risky transformation
- replace the rest3Rebuild or redesignFabric-native pipeline, T-SQL or notebooks; SSIS runtime retired
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:
-- 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;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:
- Upload the package to OneLake. Put the
.dtsx, and its.dtsConfigif it has one, into a Lakehouse Files folder such asFiles/ssis-packages/, with Upload → Upload files or OneLake file explorer. - Add the activity. In a Fabric pipeline, search the Activities pane for Invoke SSIS Package.
- Choose the package. On the Settings tab, select Browse next to Package path.
- Add configuration, if any. Select the
.dtsConfigin Configuration path (optional). - 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. - Enable logging so package-execution logs are written to OneLake; the activity output contains the logging path.
- 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
.dtsxfiles 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 INTOfrom 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:
- A third-party SFTP task downloads the day’s file to a UNC share,
\\<file-share>\inbound\. - A Script Task checks that the file exists, the header is correct and the trailer row count matches.
- Inside a Sequence Container with
TransactionOption = Required(MSDTC), an Execute SQL Task truncatesstg.Settlement, and a Data Flow (Flat File Source → Derived Column → Data Conversion → Lookup → OLE DB Destination) loads it. - An Execute SQL Task runs
etl.usp_LoadSettlement, which merges staging intodbo.Settlementand recalculatesrpt.DailySettlementon an on-premises SQL Server. - A File System Task moves the file to an archive folder.
- A Send Mail Task tells the finance team the totals are ready; an
OnErrorevent handler emails support. - 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
- SQL Agent at 06:00
- Third-party taskSFTP to UNC share
- Script Task validation
- MSDTC transactionTruncate + data flow
- usp_LoadSettlement
- Archive, Send Mail
- EliminatedCheckpoint restart
- Microsoft FabricOne pipeline, state in tables
- Schedule or file trigger
- ChangedCopy activity from SFTPLands in Lakehouse Files under a dated path
- ValidateGet Metadata plus a Lookup or notebook check
- Load stagingCopy activity or COPY INTO the Warehouse
- Must be idempotentIdempotent load procedureReplaces one business date in one transaction
- Execution log rowFile, rows, totals, status
- Alert on failure or lateness
| 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:
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;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.
- Wave 0Inventory and telemetryInventory, complexity scores, history baselines, retire list, execution log design
- gateWave 1Copy and orchestrationLow-complexity copies, simple schedules; proves connections, gateways and monitoring
- gateWave 2SQL-heavy packagesExecute SQL and stored-procedure ETL; proves Warehouse T-SQL compatibility
- gateWave 3Transformation-heavy packagesData flows rebuilt in T-SQL, Dataflow Gen2 or notebooks; proves reconciliation
- gateWave 4Scripts and custom componentsScript Tasks and third-party components, often after a hybrid period
- gateWave 5Complex operational dependenciesDeep chains, shared staging, tight SLAs, month-end and cross-team consumers
| 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
- Freeze package changes during parallel runs.
- Document the current schedule: times, dependencies, notifications, the Agent job definition.
- Run in parallel and compare with the checks above until the results match, or every remaining difference is explained and accepted by the owner.
- Disable the old scheduler for this package (the Agent job or schedule, not the package itself).
- Enable the Fabric schedule against the real target.
- Watch the first runs closely, with a named person on call.
- Keep the rollback path for the agreed window: the old job is disabled, not deleted; the old target and package still exist.
- 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
.dtsxand.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
.dtsConfigfiles. 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.
What to read next
- SQL Server to Microsoft Fabric: What Maps to What?: the wider mental model for SQL Server teams.
- Microsoft Fabric Pipelines vs Notebooks: which Fabric tool fits each part of a migrated workload.
- Microsoft Fabric Invoke Pipeline Activity: child pipelines, and invoking ADF pipelines from Fabric.
- Incremental Loads in Microsoft Fabric: watermarks,
MERGEand safe reruns instead of full reloads. - Create Your First Fabric Lakehouse and Create Your First Fabric Warehouse with T-SQL: where migrated data lands.
- Designing an Execution Log for Data Pipelines: operations after cutover.
Sources
Microsoft-documented behavior in this article comes from these Microsoft Learn pages:
- Invoke SSIS Package: Use the Invoke SSIS Package activity in a pipeline (Preview) (workflow, limits, runtime, pricing), Troubleshoot the Invoke SSIS Package activity, Integrate SSIS with SQL database in Fabric, Integrating SSIS with Fabric Data Warehouse
- Upgrade and runtimes: Upgrade planning for Azure Data Factory to Fabric Data Factory, Upgrade best practices, Differences between Data Factory in Fabric and Azure, How to access on-premises data sources in Data Factory
- Pipelines: Pipeline overview, Activity overview (retry and timeout), Run, schedule, or use events to trigger a pipeline (multiple schedules, event triggers), Invoke pipeline activity, Functions activity, Office 365 Outlook activity, Variable library integration with pipelines
- Data movement and transformation: Copy activity, Connector overview, SFTP connector, Dataflow Gen2
- Warehouse: T-SQL surface area, Transactions in Fabric Data Warehouse
- SSIS Catalog: catalog.executions (SSISDB database)
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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetMicrosoft Fabric Workspace Settings Cheat SheetThe Microsoft Fabric workspace settings that change how a data engineering workspace behaves: Spark pools, environments and jobs, domain assignment, OneLake shortcut caching and diagnostics, and data workflow (Apache Airflow job) runtime pools, with what to verify for each.Open cheat sheet
- ArticleMicrosoft Fabric Notebook Parameters: Practical Patterns for Reusable PipelinesMake one Fabric notebook serve many runs: the parameters cell, pipeline base parameters, validation, date windows, environments, secrets, safe logging and testing.Read article
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.