ETL Migration¶

Migration strategy¶

The process involves separating the transformation logic from the orchestration logic in the existing SSIS packages. Using the Replatform feature, SnowConvert AI decomposes the SSIS packages into two primary components:

• dbt projects for data transformation logic

• Snowflake TASKS or stored procedures for orchestration of the ETL workflows

The generated dbt projects and TASKS/stored procedures can then be deployed to Snowflake and executed.

Prerequisites¶

The prerequisites to use the Replatform feature for ETL migration are:

• The latest version of SnowConvert AI is installed.

• Source dependencies are accessible in Snowflake. The source dependencies are required for running the migrated dbt projects.

• DTSX (Data Transformation Services XML) package files should be extracted from ISPAC (Integration Services Project Archive) files as the ISPAC files are not directly supported.

• SSIS package version 8 or later is installed. If you have an earlier version, upgrade your packages (https://learn.microsoft.com/en-us/sql/integration-services/install-windows/upgrade-integration-services-packages-using-the-ssis-package-upgrade-wizard?view=sql-server-ver17).

Migration Steps¶

Follow these steps to migrate your SSIS project:

1. Select Browse to specify the path for the source code to be migrated. Include DDL scripts for all dependencies to ensure high-quality migrated code. The migration process uses these scripts to identify data types and constraints.

2. Select Browse to specify the path for the migrated code.

3. Select the Replatform tile to begin the migration.

4. Select Browse to navigate to your SSIS project folder containing DTSX files (required for migration).

5. Select Continue To Conversion. SnowConvert AI migrates your SSIS project and any scripts in the specified paths.

6. After migration completes, review and fix any EWIs (Errors, Warnings, and Informational messages) identified in the reports.

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

8. Review the generated output. It should include:

• 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.

9. Upload your dbt project using any one of the following options:

   • Option A: Upload using Snowflake CLI

     Run this command in your dbt project directory (replace values in italics with your schema, database, and package names):

     CopyExpand

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

     Show lessSee more

     Scroll to top

     If successful, continue to step 10.

   • 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:

     a. Select Connect > Deploy dbt project at the top right corner.

     b. Use the project name that matches your dbt project folder name. For 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.

     

     If your orchestration uses public.Package, use the following:

     CopyExpand

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

     Show lessSee more

     Scroll to top

     Use Package as your project name when deploying.

     

     Note: Deploy all dbt projects in your migration.

10. Run your dbt project by selecting the correct database and schema.

    

    For single dataflow projects:

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

    

    For multi-dataflow projects:

    a. Run the orchestration SQL file to create all TASK objects. This will create:

    • Initialization TASK and all its dependent TASKs.

    • Stored procedures corresponding to the reusable SSIS packages.

    

    b. Execute the orchestration for TASK-based orchestration (standard packages) and PROCEDURE-based orchestration (reusable packages) as shown below:

    For TASK-based orchestration

    CopyExpand

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

    Show lessSee more

    Scroll to top

    For PROCEDURE-based orchestration

    CopyExpand

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

    Show lessSee more

    Scroll to top

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

Output Structure¶

SnowConvert AI organizes all migration output under the Output/ETL/ folder. Here’s the complete folder structure:

Output/
└── ETL/
    ├── etl_configuration/
    │   ├── tables/
    │   │   └── control_variables_table.sql
    │   ├── functions/
    │   │   └── GetControlVariableUDF.sql
    │   └── procedures/
    │       └── UpdateControlVariable.sql
    ├── {PackageName}/
    │   ├── {PackageName}.sql                          # Main orchestration TASK
    │   └── {DataFlowTaskName}/                        # One dbt project per Data Flow Task
    │       ├── dbt_project.yml
    │       ├── profiles.yml
    │       ├── models/
    │       │   ├── sources.yml
    │       │   ├── staging/
    │       │   │   └── stg_raw__{component_name}.sql
    │       │   ├── intermediate/
    │       │   │   └── int_{component_name}.sql
    │       │   └── marts/
    │       │       └── {destination_component_name}.sql
    │       ├── macros/
    │       │   ├── m_update_control_variable.sql
    │       │   └── m_update_row_count_variable.sql
    │       ├── seeds/
    │       └── tests/
    └── (additional packages...)/

SSIS to SnowConvert AI conversion mapping:

• SSIS Data Flow Tasks → dbt projects (one per Data Flow Task)

• SSIS Control Flow → Snowflake TASK objects or stored procedures

• SSIS Variables → control_variables table + UDFs + DBT variables

• SSIS Containers → Inline conversion within parent TASK/procedure

Common naming and sanitization rules¶

SnowConvert AI applies consistent sanitization rules to all SSIS object names to ensure dbt and Snowflake compatibility. This includes packages, tasks, components, and variables.

These rules apply uniformly across all generated artifacts: dbt model names, Snowflake TASK names, procedure names, and variable identifiers.

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;

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

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