Migrate SSIS to Microsoft Fabric.

Every .dtsx package rebuilt on Microsoft Fabric (not lifted) and proven against the source before promotion.

SSIS does not run on Microsoft Fabric, so we rebuild it, and prove it

The single most important fact about moving SQL Server Integration Services onto Microsoft Fabric is that there’s no native SSIS runtime to move it to. A .dtsx package is an XML document describing a control flow, one or more data flows, connection managers, variables, and parameters, and that document is meaningless without the SSIS data-flow engine and the .NET runtime behind its Script Tasks. Fabric has neither. Microsoft positions Fabric as the path forward and lists the Azure-SSIS Integration Runtime as “to be determined” for Fabric; the Invoke SSIS Package activity that can run existing .dtsx files from OneLake is in preview, and is a bridge rather than a generally available lift-and-shift. So the honest framing for this pair is not migration-as-relocation but migration-as-rebuild: the fleet reads the logic out of the packages, re-expresses it natively on Fabric as dbt or in your framework, and proves the rebuild against the original before anything is promoted. Autonomous in the loop, accountable at the gates.

What a naive lift-and-shift breaks

Teams that try to treat SSIS as portable hit the same walls in the same order. The first is the data-flow engine itself. An SSIS data flow is buffer-based: rows stream through PipelineBuffer objects, a new buffer is allocated whenever an asynchronous transform (Sort, Aggregate, Merge Join) is encountered, and components are wired row-by-row. Fabric has no row-buffer model (the Warehouse is set-based T-SQL and the Lakehouse is Spark) so every transform has to be re-derived as set-based SQL or a Spark transformation, not transcribed. The second wall is Script Tasks and Script Components: arbitrary .NET (C# or VB) compiled inside the package, invisible to any schema-level mapping. Fabric Warehouse has no CLR or EXTERNAL NAME surface, so that code cannot run anywhere on the platform as-is; it has to be rewritten as a notebook, a Spark job definition, or a Function activity, under human review. The third is dynamic SQL: an Execute SQL Task whose SQLSourceType is Variable builds its statement at run time, so the SQL that actually executes is not in the file at all. A tool that reads only the static package will silently miss it. Together, Script Tasks and dynamic SQL are the two highest-risk constructs in any SSIS estate, and both are precisely the things a lift-and-shift cannot see.

How the fleet reads the package before it builds

The Documenter parses the .dtsx (or the .ispac project bundle) as the structured XML it is, but it reads the logic, not the labels. It separates the two layers that execute on different engines: the control flow of tasks and containers joined by precedence constraints, and the data flows that live inside Data Flow Tasks. For each precedence constraint it captures the Value (Success, Failure, Completion), the EvalOp (constraint, expression, or both), and the LogicalAnd flag, because an executable gated by “A succeeded AND B failed AND C succeeded” is a real branch that the rebuild must reproduce, not decoration. It resolves SSIS expressions in their own C-like language, noting footguns such as the engine representing True as -1 and refusing implicit conversion to Boolean. It follows the parameter chain through project and package parameters, environment references, and the EvaluateAsExpression flag, so the effective runtime value is captured even when the literal in the file is a placeholder. And it resolves expression-driven ConnectionString properties on connection managers rather than trusting the static string. The output is a reviewable inventory the team signs off at the assessment gate.

Two flavors, one Fabric target

From that knowledge base the builders generate one of two deliverables. On the dbt flavor the target is the Fabric Warehouse via the dbt-fabric adapter: Data Flow row transforms become set-based staging and mart models; Lookup-plus-upsert loads become incremental models on the merge strategy (the adapter default) keyed on a unique_key; SCD Type 2 transforms become dbt snapshots; Execute SQL Task side effects become pre/post-hooks or operations; and the precedence-constraint graph is replaced by the ref()/source() DAG, with tests as the quality gates. Note the adapter’s edges: ephemeral models are not supported on Fabric (the T-SQL engine lacks the nested CTEs they require), so intermediate logic is a view or table, and helpers come from tsql-utils, not dbt-utils. On your-framework flavor the rebuild is native Fabric: a Data Factory in Microsoft Fabric pipeline reproduces the control flow with If Condition, Switch, For Each, and Until; Data Flow transforms become Dataflow Gen2 (Power Query) or PySpark notebooks writing Delta into a Lakehouse or Warehouse; Lookup + SCD Type 2 becomes a Delta MERGE in a notebook or a Warehouse MERGE procedure (MERGE is GA in the Warehouse); Execute SQL Tasks become stored procedures invoked by the Stored procedure activity; and connection managers become Fabric Connections, parameterized per environment.

Iterates until green

Neither flavor is hand-waved as “done.” The test agent runs the rebuild against the SSIS baseline (row counts, column-by-column parity, SCD history reconciliation, and the failure-branch behavior) and any miss goes back to the builders inside the build-test-run loop. The iteration log on this page is representative: a trimmed-versus-untrimmed concatenation mismatch, an SCD transform that conflated a Type 1 column with the tracked Type 2 columns, an unhandled Failure-edge recovery path, each caught, each fixed, until the delta is zero. Only then does a human hold the promotion gate. If you also want to weigh the Spark-first path, Migrate SSIS to Databricks covers the same packages landing on Delta and Unity Catalog.

How the fleet runs it

SSIS to Microsoft Fabric, stage by stage.

For SSIS → Microsoft Fabric, the same five stages run with the agents that handle this pair named and linked below. Inside the loop they iterate on their own; your experts hold the three gates.

Document

Architect

The Architect

Build

Test

Operate

Build → Test → Operate iterates until green on Fabric

Orchestrated end-to-end by The Conductor , The Librarian and The Chronicler across every SSIS → Microsoft Fabric asset.

dbt or your framework

Built your way, on Fabric.

Take SSIS to Microsoft Fabric as an open-source dbt project (free to run), or built into the framework your team has trusted for years: same migration, your standards either way.

What gets generated

dbt

A dbt project (dbt_project.yml, models/, snapshots/, seeds/, tests/, macros/, sources.yml) targeting the Fabric Warehouse via the dbt-fabric adapter
Staging and mart models that re-express SSIS Data Flow row transforms as set-based T-SQL, materialized as view, table, or incremental
incremental models on the merge strategy (the adapter default) for Lookup-and-upsert loads, and dbt snapshots for SCD Type 2 history
Generic and singular tests (unique, not_null, accepted_values, relationships) plus row-count and checksum parity assertions
Generated docs and lineage from the ref()/source() DAG that replaces the precedence-constraint graph

Pattern mapping

Each SSIS pattern, mapped to a deliberate Microsoft Fabric target.

Source pattern

Microsoft Fabric · dbt

Microsoft Fabric · your framework

SSIS Data Flow: row transforms (Derived Column, Data Conversion, Conditional Split)

Set-based T-SQL in staging/intermediate dbt models (materialized view or table)

Dataflow Gen2 (Power Query) or a PySpark notebook writing Delta to the Lakehouse/Warehouse

SSIS Lookup + Slowly Changing Dimension (Type 2) transform

A dbt snapshot (timestamp or check strategy) producing dbt_valid_from/dbt_valid_to history

Notebook Delta MERGE INTO, or a Warehouse T-SQL MERGE procedure into the dimension table

SSIS Lookup feeding a Type 1 upsert (latest value wins)

An incremental model with incremental_strategy='merge' and a unique_key (adapter default)

Warehouse MERGE (GA in Fabric) in a stored procedure, keyed on the business key

SSIS Execute SQL Task / stored procedure call

Logic re-expressed as a dbt model; side effects via pre/post-hook or a dbt operation

A CREATE PROCEDURE in the Warehouse, invoked by the pipeline Stored procedure activity

SSIS Script Task / Script Component (.NET C# or VB)

No dbt equivalent, set-based logic moves to SQL; anything else is flagged for human review

A PySpark/Python notebook, Spark job definition, or Function activity, CLR/.NET is not available in Fabric

SSIS precedence constraints (Success / Failure / Completion, EvalOp, LogicalAnd)

The dbt DAG built from ref()/source(); failure/recovery handled by tests gating the run

Pipeline control-flow activities: If Condition, Switch, success/failure paths, the Fail activity

SSIS connection managers (OLE DB / ADO.NET / Flat File), expression-driven ConnectionString

sources.yml plus the profiles.yml target (the Warehouse SQL analytics endpoint connection)

Fabric Connections (replacing datasets/linked services), with properties parameterized per environment

SSIS package parameters / project parameters / environment references

dbt vars, env_var(), and per-target profiles for Dev/Test/Prod

Pipeline parameters and Fabric environment/variable libraries resolved at run time

SSIS dynamic SQL (Execute SQL Task with SQLSourceType = Variable)

Compile-time templating with dbt macros (Jinja); the executed SQL is made explicit and reviewable

Runtime dynamic T-SQL inside a Warehouse procedure, or string assembly in a notebook

SSIS Execute Package Task (cross-package orchestration)

Model selection / a parent run; cross-project ref() where a package maps to its own project

The pipeline Invoke pipeline activity (Fabric's Execute pipeline) calling a child pipeline

Before & after

A real SSIS snippet and what the builders generate on Microsoft Fabric.

LoadDimCustomer.dtsx (control flow + data flow, abbreviated) legacy

<!-- Execute SQL Task: stage today's extract -->
<DTS:Executable DTS:ExecutableType="Microsoft.ExecuteSQLTask"
    DTS:ObjectName="SQL Truncate Stage">
  <SQLTask:SqlStatementSource>TRUNCATE TABLE stg.Customer;</SQLTask:SqlStatementSource>
</DTS:Executable>

<!-- Precedence constraint: run Data Flow only on Success -->
<DTS:PrecedenceConstraint DTS:Value="Success"
    DTS:EvalOp="Constraint" DTS:LogicalAnd="True" />

<!-- Data Flow Task: OLE DB Source -> Derived Column -> Lookup -> SCD -->
<DTS:Executable DTS:ExecutableType="Microsoft.Pipeline"
    DTS:ObjectName="DFT Load DimCustomer">
  <!-- Derived Column: FullName = TRIM(FirstName) + " " + TRIM(LastName) -->
  <!-- Lookup on CustomerBK against dbo.DimCustomer -->
  <!-- Slowly Changing Dimension: Type 2 on Address, City; Type 1 on Email -->
</DTS:Executable>

dbt

snapshots/dim_customer.sql + models/staging/stg_customer.sql dbt

-- models/staging/stg_customer.sql  (Data Flow row transforms, set-based)
with src as (
    select * from {{ source('crm', 'customer') }}
)
select
    customer_bk,
    ltrim(rtrim(first_name)) + ' ' + ltrim(rtrim(last_name)) as full_name,
    email,
    address,
    city,
    updated_at
from src

-- snapshots/dim_customer.sql  (SCD Type 2 replaces the SSIS SCD transform)
{% snapshot dim_customer %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_bk',
    strategy='timestamp',
    updated_at='updated_at'
) }}
select customer_bk, full_name, email, address, city, updated_at
from {{ ref('stg_customer') }}
{% endsnapshot %}

The loop that closes itself

Iterated until green, and logged.

A failure on this pair doesn't go on a list. It goes back to the builders, and the loop re-runs until the suite passes.

ssis-to-fabric · iteration log build-test-operate

iter 1 ✗ Iteration 1, FAIL: `full_name` parity off; SSIS Derived Column trimmed before concat, generated model concatenated raw: 1,284 rows differ.

iter 2 ✗ Iteration 2, FAIL: SCD Type 2 closed too many rows; the `Email` column was Type 1 in the source SCD but treated as a tracked change, 412 spurious new versions.

iter 3 ✗ Iteration 3, FAIL: precedence `Failure` branch unhandled; the truncate-on-error recovery path had no equivalent gate.

iter 4 ✓ Iteration 4, GREEN: row counts match, SCD history reconciles, recovery path reproduced, delta 0 against the SSIS baseline.

Green on iteration 4: row counts, SCD Type 2 history and the failure branch all reconcile to the SSIS baseline.

Questions for this migration

SSIS → Microsoft Fabric, answered.

Can you lift my SSIS packages and run them on Microsoft Fabric as-is?

No, and we will not claim otherwise. There is no generally available SSIS runtime on Fabric. Microsoft lists the Azure-SSIS Integration Runtime as "to be determined" for Fabric, and the **Invoke SSIS Package activity** that runs existing `.dtsx` files from OneLake is in **preview**: a bridge, not a GA lift-and-shift. The fleet rebuilds the logic natively on Fabric and proves it against the source. If you need a temporary bridge while the rebuild lands, that is a design-gate decision, not a default we assume.

How does an SSIS Slowly Changing Dimension (Type 2) transform get rebuilt?

On the dbt flavor it becomes a dbt `snapshot`, which is dbt's built-in Type 2 SCD: it closes the old row and inserts a new version, maintaining `dbt_valid_from`, `dbt_valid_to`, and `dbt_scd_id`. Use the `timestamp` strategy when there is a reliable `updated_at`, or the `check` strategy comparing `check_cols` when there is not. On your-framework flavor it becomes a Delta `MERGE` in a notebook (or a `MERGE` procedure in the Warehouse, where `MERGE` is GA) that expires the current version and inserts the new one. Mixed Type 1 / Type 2 columns in one SSIS SCD transform are split deliberately (Type 1 columns overwrite, Type 2 columns version) and the iteration log catches it when they are conflated.

What happens to a Script Task written in C# or VB.NET?

It is flagged for human review, because it is opaque custom code with no declarative equivalent, and Fabric Warehouse has no CLR/.NET in-engine surface. Where the Script Task's logic is genuinely set-based, the fleet re-expresses it as SQL. Where it is not (calling an external API, parsing a bespoke file format, custom hashing) it moves to a PySpark or Python notebook, a Spark job definition, or a Function activity, and a human approves the rewrite at the design gate. Script Tasks and dynamic SQL are the two highest-risk SSIS constructs, so they are never auto-converted blind.

SSIS precedence constraints have Success, Failure and Completion edges. How are those preserved?

On the dbt flavor, ordering comes from the DAG that dbt builds from `ref()` and `source()`, and the equivalent of a quality gate is dbt tests failing the run before bad data propagates. On your-framework flavor the control flow maps to pipeline activities (`If Condition`, `Switch`, `For Each`, `Until`) with explicit success and failure paths and the `Fail` activity for deliberate aborts. The subtle part is `LogicalAnd` and `EvalOp`: an executable gated by "A succeeded AND B failed" is a real branch, and the Documenter records it so the rebuild reproduces the recovery route, not just the happy path.

We use a lot of dynamic SQL in Execute SQL Tasks. Does that survive the move?

It is read first, then re-expressed. When `SQLSourceType = Variable`, the executed statement is assembled at run time and is not present statically in the package, so the Documenter traces how the string is built before anything is generated. On the dbt flavor the idiomatic replacement is a Jinja `macro` that templates the SQL at compile time, which makes the generated statement explicit and reviewable. Where the SQL genuinely must be built at run time, it stays as dynamic T-SQL inside a Warehouse procedure, or as string assembly in a notebook, never silently dropped.

Do connection managers and package parameters carry over?

Conceptually, yes; literally, no. SSIS connection managers become Fabric Connections (which replace ADF-style datasets and linked services, with properties set inline per activity) on your-framework flavor, or `sources.yml` plus the Warehouse connection in `profiles.yml` on dbt. Because an SSIS `ConnectionString` is often expression- or parameter-driven, the literal string in the `.dtsx` is not necessarily what runs; the Documenter resolves the property expressions and the parameter-to-environment-reference chain so the effective value is captured, then re-parameterizes it per environment on the target.

Ready to migrate SSIS to Microsoft Fabric?

Tell us about your SSIS estate and we'll run the assessment, the Surveyor scores it before you commit to Microsoft Fabric.

Plan my migration

A short form, no spam. We usually reply within one business day.