SnowConvert AI - ETL Migration¶

Step 1: Select the path to your scripts¶

Include DDL scripts for all dependencies to ensure high-quality migrated code. The migration process uses these scripts to identify data types and constraints.

Step 2: Click the Replatform card¶

Click the Replatform card to begin the migration. If you don’t see this option, update SnowConvert AI to the latest version, or contact support.

Step 3: Browse to your SSIS project location¶

1. Click Browse and navigate to your SSIS project folder

2. Ensure the folder contains DTSX files (required for migration)

3. Click Continue To Conversion

SnowConvert AI migrates your SSIS project and any scripts in the specified paths.

Step 4: Review the results¶

After migration completes:

1. Review the migration reports

2. Fix any issues identified in the reports

3. Fill placeholders in sources.yml, profiles.yml, and dbt_project.yml

The output includes:

• ETL/: Main folder containing all converted SSIS packages

  • etl_configuration/: Infrastructure components (control_variables table, UDFs, procedures)

  • {PackageName}/: Folder for each SSIS package containing:

    • {PackageName}.sql: Orchestration file (TASK or PROCEDURE)

    • {DataFlowTaskName}/: dbt project for each Data Flow Task

• script.sql: Migrated SQL scripts (if applicable)

For a detailed description of the output structure, see Output Structure.

Step 5: Upload your dbt project¶

After you’ve reviewed the dbt project, filled placeholders in .yml files, and fixed EWIs, upload the project using one of these methods.

For more information about working with dbt projects on Snowflake, see Getting Started with dbt Projects.

snow dbt deploy --schema schema_name --database database_name --force package_name

Option B: Upload via Snowflake Workspace¶

Navigate to Workspaces > Add new > Upload Folder and select your dbt project folder.

Deploy the dbt project to make it accessible for orchestration:

1. Click Connect > Deploy dbt project (top right corner)

2. Use the project name that matches your dbt project folder name

   • Example: For Process_Sales_Files_Load_Sales_Data/, use “Process_Sales_Files_Load_Sales_Data”

   • This name is referenced in the orchestration file via EXECUTE DBT PROJECT commands

For example, if your orchestration uses public.Package:

EXECUTE DBT PROJECT public.Package ARGS='build --select tag:package_dataflowtask --target dev';

Copy

Use Package as your project name when deploying.

Note: Deploy all dbt projects in your migration.

Step 6: Run your dbt project¶

Select the correct database and schema before running your project.

For single dataflow projects:

Run the dbt project directly if you have only one data flow.

For multi-dataflow projects:

1. Run the orchestration SQL file to create all TASK objects

   • This creates the initialization TASK and all dependent TASKs

   • For reusable packages, this creates stored procedures instead

2. Execute the orchestration:

   For TASK-based orchestration (standard packages):

-- Execute the root task
EXECUTE TASK public.Package;

For PROCEDURE-based orchestration (reusable packages):

-- Call the stored procedure
CALL public.PackageName();

Note: Check your generated SQL file to determine whether your package uses the TASK or PROCEDURE pattern.

Layer Organization¶

Each dbt project follows a three-tier architecture that separates data extraction, transformation, and loading:

Materialization configuration:

Default materializations are defined in dbt_project.yml. However, individual models can override these defaults when needed:

• Use {{ config(materialized='view') }} to change a specific model’s materialization

• Use {{ config(alias='...') }} in mart models to customize the final table name in Snowflake

dbt Model Naming Conventions¶

SnowConvert uses prefixes to indicate each model’s layer in the dbt project:

The stg_raw__ prefix indicates a staging model that selects from a raw source, while the int_ prefix marks intermediate transformation models. Mart models use the destination table name directly or can specify a custom alias.

Important notes:

• All component names are sanitized according to the naming rules above

• Mart models become the final table names in Snowflake

• You can customize mart table names using {{ config(alias='TableName') }}

dbt Project Organization¶

Organization structure:

• One dbt project per Data Flow Task (e.g., Process_Sales_Files_Load_Sales_Data/)

• Package-level folder contains the orchestration SQL file and all dbt project folders

• Models organized by layer (staging, intermediate, marts) within each dbt project

• Orchestration execution uses EXECUTE DBT PROJECT commands

sources.yml Configuration¶

The sources.yml file, located in the models/ directory, declares all source tables used by the dbt project. This file serves three key purposes:

• Connection: Links dbt models to raw data tables in Snowflake

• Documentation: Provides metadata and descriptions for source systems

• Lineage: Enables tracking data flow from sources through transformations

Important: Before deploying your dbt project, replace the YOUR_SCHEMA and YOUR_DB placeholders with your actual Snowflake schema and database names.

dbt Macros¶

Each dbt project includes these macros:

Orchestration Naming Conventions¶

Orchestration objects follow consistent naming patterns based on the SSIS package and task names:

Notes:

• All names are sanitized according to the naming rules described earlier

• Stored procedures are used when packages are referenced multiple times or have parameter mappings

Orchestration Approach¶

Each SSIS package generates an orchestration SQL file. The conversion pattern depends on whether the package is reused:

CREATE OR REPLACE TASK public.Package AS
BEGIN
   -- Initialize control variables
   DELETE FROM public.control_variables WHERE variable_scope = 'Package';
   INSERT INTO public.control_variables ...
END;

CREATE OR REPLACE TASK public.package_data_flow_task
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.package
AS
BEGIN
   -- Declare LET variables from control table
   LET User_Variable VARCHAR := public.GetControlVariableUDF('User_Variable', 'Package') :: VARCHAR;
   
   -- Execute dbt project
   EXECUTE DBT PROJECT public.My_DataFlow_Project ARGS='build --target dev';
   
   -- Update control variables
   CALL public.UpdateControlVariable('User_Variable', 'Package', TO_VARIANT(:User_Variable));
END;

Variable Management¶

SSIS variables are converted into a comprehensive management system using four interconnected mechanisms:

LET MyVar VARCHAR := public.GetControlVariableUDF('MyVar', 'Package') :: VARCHAR;

CALL public.UpdateControlVariable('MyVar', 'Package', TO_VARIANT(:MyVar));

Inline Container Conversion Approach¶

SnowConvert uses an inline conversion approach for SSIS containers (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/control-flow?view=sql-server-ver17) rather than creating separate procedures. This architectural decision preserves execution context and simplifies the migration.

Why inline conversion?

Migrating SSIS isn’t just “translate this component to that component.” It’s untangling control flow, variables, and data movement that have lived together for years. Our inline approach preserves that context:

• One place to debug: Containers and branches are converted inline inside parent Snowflake procedures or tasks. No bouncing across tools to understand why something ran (or didn’t).

• Deterministic orchestration: Single-use packages become Snowflake Tasks with explicit dependencies. Reusable packages (invoked multiple times or parameterized) become procedures for clean, synchronous reuse.

• Fewer naming collisions: We consistently sanitize object names across dbt models, tasks, procedures, and variables, so deployments remain predictable in shared environments.

• Familiar, but modern: Data movement and business logic land in dbt with layered models and macros, while orchestration runs natively on Snowflake. Same mental model as SSIS—without the engine lock-in.

What gets converted inline:

• Sequence Containers (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/sequence-container?view=sql-server-ver17) - Sequential task execution with marked boundaries

• For Loop Containers (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/for-loop-container?view=sql-server-ver17) - Container structure preserved, iteration logic requires manual implementation

• ForEach Loop Containers (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-ver17) - File enumerators converted to Snowflake stage operations, other types require manual work

• Event Handlers (https://learn.microsoft.com/en-us/sql/integration-services/integration-services-ssis-event-handlers?view=sql-server-ver17) - Not supported; implement using Snowflake exception handling

For detailed conversion specifications, examples, and EWI/FDM references for all control flow elements and task conversions, see the SSIS Translation Reference.