SnowConvert AI - SSIS¶

Sequence Containers¶

Sequence containers (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/sequence-container?view=sql-server-ver17) are converted inline within the parent TASK. The container’s boundaries are marked with comments in the generated code, and all tasks within the container execute sequentially in the same TASK scope.

Conversion characteristics:

• No separate procedure or TASK is created for the container

• Container boundaries are clearly marked BEGIN … END blocks

• All tasks execute sequentially within the parent TASK

• Task execution order based on precedence constraints is maintained

• Limitation: Only “Success” precedence constraints are fully supported. Conditional execution based on task outcomes (Failure or Completion constraints) is not currently implemented and will require manual post-migration adjustments

Behavioral differences:

• FDM generated: SSC-FDM-SSIS0003

• Variable scoping differs from SSIS: Container variables are accessible throughout the entire parent TASK, not just within the container scope

Example:

-- BEGIN Sequence Container: MySequence
-- Task 1 within sequence
EXECUTE DBT PROJECT public.DataFlow1 ARGS='build --target dev';
-- Task 2 within sequence  
EXECUTE DBT PROJECT public.DataFlow2 ARGS='build --target dev';
-- END Sequence Container: MySequence

For Loop Containers¶

For Loop containers (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/for-loop-container?view=sql-server-ver17) are converted to sequential execution of their contained tasks. However, the loop iteration logic itself requires manual implementation.

Conversion limitations:

• The container executes once by default (iteration logic not automatically converted)

• InitExpression, EvalExpression, and AssignExpression require manual conversion

• An EWI (SSC-EWI-SSIS0004) is generated to indicate manual work is needed

Required manual steps:

1. Review EvalExpression to understand the loop termination condition

2. Implement the iteration using Snowflake’s WHILE loop construct

3. Update AssignExpression logic for proper loop counter management

ForEach Loop Containers¶

File Enumerator (Supported)

ForEach File Enumerator containers (https://learn.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-ver17) are converted to Snowflake stage operations using the LIST command and cursor pattern:

-- List files from Snowflake stage
LIST @<STAGE_PLACEHOLDER>/FolderPath PATTERN = '.*/file_pattern\.csv';

-- Create cursor for iteration
LET file_cursor CURSOR FOR
   SELECT REGEXP_SUBSTR($1, '[^/]+$') AS FILE_VALUE
   FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
   WHERE $1 NOT LIKE '%FolderPath/%/%';

-- Iterate through files
FOR file_row IN file_cursor DO
   User_CurrentFileName := :file_row.FILE_VALUE;
   EXECUTE DBT PROJECT public.My_DataFlow_Project ARGS='build --target dev';
END FOR;

Configuration requirements:

After migration, you’ll need to:

• Replace <STAGE_PLACEHOLDER> with your actual Snowflake stage name

• Ensure the folder path is correctly mapped to a Snowflake stage

• Verify that files are properly staged in Snowflake

An EWI (SSC-EWI-SSIS0014) is generated to remind you of this manual configuration step.

Other Enumerator Types

Other ForEach enumerator types (ForEach Item, ForEach ADO, ForEach NodeList, etc.) aren’t currently supported. SnowConvert generates an EWI (SSC-EWI-SSIS0004) for these cases. Consider implementing the equivalent logic using Snowflake queries or scripting constructs.

Event Handlers¶

Event handlers (https://learn.microsoft.com/en-us/sql/integration-services/integration-services-ssis-event-handlers?view=sql-server-ver17) (OnError, OnWarning, OnPreExecute, OnPostExecute, etc.) aren’t supported. EWIs are generated. Implement manually using Snowflake exception handling.