Migration
SSIS to Snowflake migration: replacing SSIS packages with ELT in Snowflake
How to move SQL Server Integration Services packages to Snowflake: land raw data, rebuild data flows as SQL and dbt, replace OLE DB Command with MERGE, Script Tasks with Snowpark, and SQL Agent with Snowflake Tasks.
Hemant Walchale
· 2 min read
Many SQL Server shops move their warehouse to Snowflake and then face the SSIS packages that fed it. Keeping SSIS means keeping a Windows server, an SSIS runtime and a row-based transformation engine in front of a platform that transforms data far faster in SQL. Rebuilding as ELT inside Snowflake is usually simpler than it looks.
This is the approach we recommend.
1. Inventory
Export the .dtsx packages from SSISDB and count component types:
grep -ohE 'componentClassID="[^"]+"' *.dtsx | sort | uniq -c | sort -rn
Collect SQL Server Agent schedules, project and package parameters, SSISDB environments and execution history. Retire packages that no longer run.
2. Land raw data first
Replace source components with loading into raw Snowflake tables: a replication or CDC tool for databases, Snowpipe or COPY INTO for files, and connectors for SaaS sources. Transformations then run on data already inside Snowflake.
3. Map the data flow to SQL
| SSIS component | In Snowflake |
|---|---|
| Derived Column, Data Conversion | SQL expressions and CAST |
| Conditional Split | WHERE per output, or CASE feeding separate models |
| Lookup | JOIN (the no-match output becomes a LEFT JOIN with a null check) |
| Merge Join, Sort | JOIN; no sort needed |
| Aggregate | GROUP BY |
| Union All | UNION ALL |
| Slowly Changing Dimension | dbt snapshots or MERGE |
| OLE DB Command (row-by-row updates) | One set-based MERGE; usually far faster |
| Script Component | SQL, or Snowpark |
| Destinations | Tables or incremental dbt models |
Most teams organize the result as dbt models (raw, staged, marts) with tests, rather than copying package structure.
4. Replace control flow
Execute SQL Tasks become SQL steps or models; precedence constraints become Task dependencies or dbt model dependencies; Foreach Loops become set-based SQL or orchestrator loops; Script Tasks become Snowpark procedures; Send Mail becomes alerts or notifications; SQL Agent schedules become Snowflake Tasks or orchestrator schedules.
5. Reconcile and retire
Compare every target table against the SSIS-loaded version. Watch collation (SQL Server compares text case-insensitively by default, Snowflake does not), datetime precision, implicit conversions in Derived Columns and Lookup no-match handling. Disable packages and Agent jobs on each domain’s cutover day.
The short version
Land raw data in Snowflake, rebuild data flows as SQL and dbt models, replace OLE DB Command with MERGE and scripts with Snowpark, move schedules to Snowflake Tasks, handle collation deliberately, reconcile and retire SSIS by domain.
See also SQL Server to Snowflake and SSIS to Databricks, or browse all migration guides.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Snowflake. Unfamiliar terms are explained in the data engineering glossary.