Migration
SSIS to Databricks migration: how to convert SQL Server Integration Services packages
Moving SSIS packages to Databricks: inventory .dtsx files, map data flow components to Spark SQL and Delta, rebuild control flow and SQL Agent schedules as Lakeflow Jobs, and reconcile before cutover.
Hemant Walchale
· 3 min read
SQL Server Integration Services (SSIS) has run a great deal of Microsoft-shop ETL for twenty years. When the warehouse moves to Databricks, the packages that feed it need rebuilding, because SSIS packages only run on the SSIS runtime. The good news is that most SSIS logic maps cleanly to SQL.
This is the approach we recommend.
1. Inventory the packages
SSIS packages (.dtsx) are XML, deployed to the SSIS catalog (SSISDB) or the file system. Export them, then count the component types across the estate:
grep -ohE 'componentClassID="[^"]+"' *.dtsx | sort | uniq -c | sort -rn
Also collect the SQL Server Agent jobs that run them (schedules and step order), project and package parameters, SSISDB environments (connection strings per environment), and the execution history that shows which packages still run.
2. Map data flow components
| SSIS data flow component | On Databricks |
|---|---|
| OLE DB, ADO.NET, Flat File, Excel sources | Ingest into bronze Delta tables (Lakeflow Connect, Auto Loader for files, or JDBC reads) |
| Derived Column, Data Conversion | SQL expressions and CAST |
| Conditional Split | WHERE per output, or a CASE column feeding separate writes |
| Lookup | A join; broadcast small reference tables |
| Merge Join, Sort | A join; no sort needed |
| Aggregate | GROUP BY |
| Union All | UNION ALL |
| Slowly Changing Dimension | AUTO CDC with SCD type 2 in Lakeflow Declarative Pipelines, or MERGE |
| OLE DB Command (row-by-row updates) | One set-based MERGE; usually much faster |
| Script Component | Rewrite in SQL or Python |
| OLE DB and SQL Server destinations | Writes to Delta tables |
3. Rebuild control flow as Lakeflow Jobs
- Data Flow Tasks become pipeline or notebook tasks.
- Execute SQL Tasks become SQL tasks.
- Precedence constraints (success, failure, completion, expressions) become task dependencies with run-if conditions and if/else condition tasks.
- Foreach Loop containers become for-each tasks or a loop inside one task.
- Script Tasks become Python tasks.
- Send Mail Tasks become job notifications.
- File System and FTP Tasks usually disappear: files land in cloud storage and Auto Loader picks them up.
- SQL Server Agent schedules become job schedules or file-arrival triggers.
- Parameters and SSISDB environments become job parameters and per-environment configuration in version control.
4. Reconcile, then cut over by domain
Run the new pipelines next to SSIS against the same sources and compare every target table. Common differences: implicit conversions in Derived Columns, collation and trailing spaces, NULL handling in Lookups (the “no match” output), and datetime precision. Cut over a domain once comparisons are clean and its owner signs off, then disable the matching Agent jobs the same day.
The short version
Export the packages, count component types, map data flows to SQL and Delta, replace row-by-row commands with set-based merges, rebuild control flow and Agent schedules as Lakeflow Jobs, rewrite scripts and reconcile every table before switching off each job.
Moving SSIS to Microsoft Fabric instead? See our SQL Server and SSIS to Fabric guide, or browse all migration guides. For a fixed-price plan, see the Data Platform Assessment.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Databricks. Unfamiliar terms are explained in the data engineering glossary.