SSIS to dbt: Convert .dtsx Packages into dbt Models

Split the package before translating it

A .dtsx package has two execution layers. The control flow decides which tasks run, in what order, and under which conditions. Inside a Data Flow Task, the data flow reads rows, derives columns, filters, joins, aggregates, and writes results. dbt has a direct home for the second layer because a model is a SQL transformation with declared dependencies.

Precedence constraints, Foreach containers, Script Tasks, and package variables don't become model SQL. Keep those items in the migration inventory, then rebuild the required scheduling in dbt jobs, Airflow, Azure Data Factory, or the warehouse scheduler your team already owns.

What becomes a dbt model

OLE DB, flat-file, and Excel inputs become source declarations or staging inputs. Derived Column steps become selected expressions; Conditional Split outputs become filtered branches; Lookup and Merge Join steps become joins; Aggregate and Union All become their SQL equivalents. Each generated model keeps the original package and task names nearby so an engineer can trace the code back to the canvas.

• Raw inputs become source() references in sources.yml
• Model SQL uses ref() when one generated model depends on another
• schema.yml records model and column metadata for review
• dbt_project.yml supplies the project shell and model path

Warehouse dialect still decides whether the model runs

dbt doesn't erase SQL dialect differences. BigQuery expects backtick quoting and UNNEST patterns; Snowflake supports QUALIFY and its own date functions; PostgreSQL has different casting and lateral behavior. Pick the warehouse target before generation so the project doesn't begin with a cross-dialect cleanup pass.

Materialization also needs a human decision. A converter can place a safe default in config, but it can't infer whether a production model should be a view, table, incremental model, or ephemeral helper from the package alone.

Review the branches SSIS makes easy to miss

Conditional Split has a default output for rows that match none of its named expressions. Lookup can route unmatched rows down a separate path. Those branches often carry reject handling or audit records, and dropping one changes row counts without producing an obvious syntax error.

Deflows keeps control-flow tasks and unsupported script behavior visible instead of folding them into plausible-looking SQL. The dbt targets are beta, so generated files should enter the same pull-request and warehouse-test path as hand-written models.

Try a downloadable SSIS to dbt example

This synthetic .dtsx fixture reads Orders, derives SalesWithTax as Sales × 1.2, and records an OrdersOut destination. It also contains a separate Truncate staging task. The example keeps that task outside the dbt row transformation, so you can inspect the migration boundary alongside the model.

The local Deflows parser and deterministic PostgreSQL generator produced the SQL below. The production Convert API and native Alteryx/SSIS runtimes were not run. These source files are parser fixtures; native execution has not been certified. Web Convert may produce a different query. The download includes the source, synthetic input, generated output, expected rows, a verification script and provenance.

• Download the complete example (ZIP)
• Source package (.dtsx)
• Synthetic input (CSV)
• Generated dbt model (SQL)
• Expected rows (JSON)
• Verification script (SQL)
• Run instructions and limits

How does the SSIS data flow become a dbt model?

The generated model keeps the original OrderID, Region and Sales columns and adds SalesWithTax. The scaffold uses source('source_db', 'dbo_orders'), declared in sources.yml under the raw schema. Moving [dbo].[Orders] to raw.dbo_orders is an explicit warehouse mapping; the original case-sensitive column names stay intact. The complete ZIP also contains dbt_project.yml, schema.yml and sources.yml from the Deflows scaffold exporter.

For the local row check, compiled.sql substitutes a temporary PostgreSQL table for source() and removes config(). A dbt compile or build was not run. To run the scaffold yourself, configure a default dbt profile for a disposable PostgreSQL target, load raw.dbo_orders, and run dbt build as described in the README. The dbt target remains beta.

{{ config(materialized='table') }}

WITH ole_db_source AS (
  SELECT
    "OrderID",
    "Region",
    "Sales"
  FROM {{ source('source_db', 'dbo_orders') }}
),
derived_column AS (
  SELECT
    "OrderID",
    "Region",
    "Sales",
    "Sales" * 1.2 AS "SalesWithTax"
  FROM ole_db_source
)
SELECT * FROM derived_column
ORDER BY "OrderID"

Check the expected rows yourself

Extract the ZIP and run the following command with PostgreSQL and psql available. verify.sql creates temporary input tables, executes the SQL and compares every output column in both directions with EXCEPT ALL, preserving duplicate counts. It rolls the transaction back when finished. The expected result is PASS.

psql -X -v ON_ERROR_STOP=1 -f verify.sql

• Positive, zero and negative Sales values multiply by 1.2; NULL remains NULL.
• Local PostgreSQL row execution checks the SQL body, while dbt profile configuration and warehouse execution remain separate checks.
• Truncation, scheduling, error paths, destination append/merge behavior and materialization equivalence need their own migration decisions.

What this example verifies

The exported SQL was executed locally against the included synthetic input using PostgreSQL in PGlite and matched the independently specified expected rows. Changing an expected amount made the row check fail. This demonstrates the stated transformation on this fixture and input.

Alteryx/SSIS source output comparison, native runtime execution and the Deflows Parity Test Kit were not run. This example does not establish a general conversion accuracy rate or a production parity guarantee.