SnowConvert AI - SSIS Functional Differences¶

Severity¶

None

Description¶

This FDM is generated when a Lookup transformation is converted to SQL JOIN. In SSIS, the Lookup transformation returns the first matching row based on the order rows are read from the reference table. In standard SQL, when multiple rows match the join condition without an ORDER BY clause, any matching row may be returned, making the result non-deterministic.

To ensure consistent behavior matching SSIS, add an ORDER BY clause to the query that retrieves the first match.

Example Code¶

<component componentClassID="Microsoft.Lookup" name="Lookup Customer">
  <properties>
    <property name="SqlCommand">
      SELECT CustomerID, CustomerName, CustomerTier 
      FROM DimCustomer
    </property>
  </properties>
  <inputs>
    <input name="Lookup Input">
      <inputColumns>
        <inputColumn lineageId="100">
          <properties>
            <property name="JoinToReferenceColumn">CustomerID</property>
          </properties>
        </inputColumn>
      </inputColumns>
    </input>
  </inputs>
</component>

WITH first_match_check AS
(
   SELECT
      COUNT(*) > 1 AS has_multiple_matches
   FROM
      (
         SELECT
            --** SSC-FDM-SSIS0001 - REPLACE NULL WITH APPROPRIATE ORDER BY COLUMN(S) TO ENSURE DETERMINISTIC FIRST MATCH SELECTION. **
            null
      )) = 1
),
input_data AS
(
   SELECT
      order_id,
      customer_id
   FROM
      {{ ref('stg_raw__orders') }}
)
SELECT
   input_data.order_id,
   input_data.customer_id,
   lookup_data.customer_name,
   lookup_data.customer_tier
FROM
   input_data
   LEFT JOIN {{ ref('stg_dimcustomer') }} AS lookup_data
      ON input_data.customer_id = lookup_data.customer_id

Best Practices¶

• Identify the column(s) that determine the order in the source Lookup table

• Replace null with appropriate ORDER BY columns (e.g., ORDER BY modified_date DESC, customer_id)

• Test with data that has multiple matches to verify deterministic behavior

• Document the ordering logic for future maintenance

• If you need more support, you can email us at snowconvert-support@snowflake.com

Severity¶

Low

Description¶

This FDM is generated when a Merge transformation is converted to UNION ALL. In SSIS, the Merge transformation requires sorted inputs and naturally produces a sorted, deterministic output preserving the merge order. The equivalent SQL UNION ALL does not guarantee any particular order unless an explicit ORDER BY clause is added.

If the order of rows matters for downstream processing or matches SSIS behavior, add an ORDER BY clause to the final query.

Example Code¶

<component componentClassID="Microsoft.Merge" name="Merge">
  <inputs>
    <input name="Merge Input 1">
      <inputColumns>
        <inputColumn sortKeyPosition="1" name="ProductID" />
      </inputColumns>
    </input>
    <input name="Merge Input 2">
      <inputColumns>
        <inputColumn sortKeyPosition="1" name="ProductID" />
      </inputColumns>
    </input>
  </inputs>
</component>

-- !!!SSC-FDM-SSIS0002 - ADD AN ORDER BY CLAUSE TO ENSURE SORTED OUTPUT. **
WITH source1 AS (
   SELECT ProductID, ProductName, Price
   FROM {{ ref('stg_products') }}
),
source2 AS (
   SELECT ProductID, ProductName, Price
   FROM {{ ref('stg_new_products') }}
)
SELECT * FROM source1
UNION ALL
SELECT * FROM source2
-- Add ORDER BY ProductID if sorted output is required

Best Practices¶

• Determine if sorted output is required for downstream processing

• Add ORDER BY clause matching the original SSIS sort keys if order matters

• Consider performance implications of sorting large result sets

• Document whether sorting is necessary for correctness or just for consistency

• If you need more support, you can email us at snowconvert-support@snowflake.com

Severity¶

None

Description¶

This FDM indicates that an SSIS container (Sequence Container, For Loop, or ForEach Loop) was converted inline rather than as a separate procedural block. In SSIS, containers create variable scopes and logical groupings. In the Snowflake conversion, container contents are expanded inline within the parent execution context.

This approach offers benefits:

• Improved debugging (direct visibility of all steps)

• Better performance (reduced nesting overhead)

• Simplified execution flow

However, variable scoping works differently—variables are in the parent scope rather than container scope.

Example Code¶

<DTS:Executable DTS:CreationName="STOCK:SEQUENCE"
  DTS:ObjectName="Sequence Container">
  <DTS:Executables>
    <DTS:Executable DTS:CreationName="Microsoft.ExecuteSQLTask"
      DTS:ObjectName="Execute SQL Task 1" />
    <DTS:Executable DTS:CreationName="Microsoft.ExecuteSQLTask"
      DTS:ObjectName="Execute SQL Task 2" />
  </DTS:Executables>
</DTS:Executable>

CREATE OR REPLACE TASK package_main
WAREHOUSE=DUMMY_WAREHOUSE
AS
BEGIN
   --** SSC-FDM-SSIS0003 - THE SSIS 'SEQUENCE' CONTAINER WAS CONVERTED INLINE. Original container name: Package\Sequence Container **
   BEGIN
      -- Execute SQL Task 1
      INSERT INTO staging_table SELECT * FROM source_table1;
      
      -- Execute SQL Task 2
      INSERT INTO target_table SELECT * FROM staging_table;
   END;
END;

Best Practices¶

• Review variable scope changes if the container had local variables

• Verify that inline conversion doesn’t affect error handling or transaction boundaries

• Benefits from inline conversion include simpler debugging and execution paths

• No action required unless specific container scoping was critical to logic

• If you need more support, you can email us at snowconvert-support@snowflake.com

Severity¶

Low

Description¶

This FDM is generated when a Merge Join transformation is converted to a standard SQL JOIN. In SSIS, the Merge Join transformation requires sorted inputs and naturally produces a sorted, deterministic output based on the join keys and the merge algorithm. The equivalent SQL JOIN does not guarantee any particular order unless an explicit ORDER BY clause is added.

If the order of rows matters for downstream processing or to match SSIS behavior exactly, add an ORDER BY clause.

Example Code¶

<component componentClassID="Microsoft.MergeJoin" name="Merge Join">
  <inputs>
    <input name="Merge Join Left Input">
      <inputColumns>
        <inputColumn sortKeyPosition="1" name="EmployeeID" />
      </inputColumns>
    </input>
    <input name="Merge Join Right Input">
      <inputColumns>
        <inputColumn sortKeyPosition="1" name="EmployeeID" />
      </inputColumns>
    </input>
  </inputs>
  <properties>
    <property name="JoinType">1</property> <!-- Inner Join -->
  </properties>
</component>

SELECT
   --** SSC-FDM-SSIS0004 - ADD AN ORDER BY CLAUSE TO ENSURE SORTED OUTPUT. THE SSIS MERGE JOIN TRANSFORMATION ASSUMES SORTED INPUTS AND NATURALLY PRODUCES A SORTED, DETERMINISTIC OUTPUT. THE EQUIVALENT SQL JOIN DOES NOT GUARANTEE ORDER. **
   employeeassignments.employee_id,
   tasks.project_id AS "project identifier",
   employeeassignments.assignment_start_date,
   employeeassignments.assigned_hours,
   tasks.task_id
FROM
   {{ ref('stg_employee_assignments') }} AS employeeassignments
   INNER JOIN {{ ref('stg_tasks') }} AS tasks
      ON employeeassignments.task_id = tasks.task_id
-- Add ORDER BY employee_id, task_id if sorted output is required

Best Practices¶

• Determine if sorted output is needed for downstream processing

• Add ORDER BY clause on the join keys if order matters

• Consider performance implications of sorting, especially on large datasets

• Test with representative data volumes

• If you need more support, you can email us at snowconvert-support@snowflake.com

Severity¶

None

Description¶

This FDM indicates that an SSIS package was converted to a Snowflake stored PROCEDURE instead of a TASK because it is called by multiple parent packages (Execute Package Tasks). This design choice provides several benefits:

Benefits of PROCEDURE over TASK:

• Synchronous execution: Calling packages wait for completion (matches SSIS behavior)

• Reusability: Can be called from multiple locations with different parameters

• Return values: Can return status codes or result sets to callers

• Simpler orchestration: Direct CALL statements instead of complex EXECUTE TASK chains

Difference from SSIS:

• Must be explicitly called with CALL procedure_name() instead of automatic execution

• Parameters must be passed explicitly in the CALL statement

• No automatic task scheduling (must be invoked programmatically)

Example Code¶

<!-- Reusable package referenced by multiple parent packages -->
<DTS:Executable DTS:ObjectName="UtilityPackage">
  <DTS:Variables>
    <DTS:Variable DTS:Name="InputParam" DTS:DataType="8" />
  </DTS:Variables>
  <DTS:Executables>
    <DTS:Executable DTS:CreationName="Microsoft.ExecuteSQLTask">
      <!-- Task implementation -->
    </DTS:Executable>
  </DTS:Executables>
</DTS:Executable>

<!-- Parent Package 1 -->
<DTS:Executable DTS:CreationName="Microsoft.ExecutePackageTask">
  <ExecutePackageTask>
    <PackageName>UtilityPackage.dtsx</PackageName>
  </ExecutePackageTask>
</DTS:Executable>

<!-- Parent Package 2 -->
<DTS:Executable DTS:CreationName="Microsoft.ExecutePackageTask">
  <ExecutePackageTask>
    <PackageName>UtilityPackage.dtsx</PackageName>
  </ExecutePackageTask>
</DTS:Executable>

-- !!!SSC-FDM-SSIS0005 - PACKAGE WAS CONVERTED TO STORED PROCEDURE BECAUSE IT IS BEING REUSED BY OTHER PACKAGES. **
CREATE OR REPLACE PROCEDURE public.utilitypackage(input_param VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
   -- Package logic here
   INSERT INTO log_table VALUES (CURRENT_TIMESTAMP(), :input_param);
   RETURN 'SUCCESS';
END;
$$;

-- Parent Package 1 calls the procedure
CREATE OR REPLACE TASK public.parent_package_1_execute_utility
WAREHOUSE=DUMMY_WAREHOUSE
AS
BEGIN
   CALL public.utilitypackage('param_value_1');
END;

-- Parent Package 2 calls the procedure
CREATE OR REPLACE TASK public.parent_package_2_execute_utility
WAREHOUSE=DUMMY_WAREHOUSE
AS
BEGIN
   CALL public.utilitypackage('param_value_2');
END;

Best Practices¶

• Use CALL statements to invoke the procedure from parent packages

• Pass parameters explicitly in the CALL statement

• Implement error handling in the procedure to return appropriate status codes

• Consider capturing return values for error handling and logging

• Test the procedure with all calling contexts to ensure consistent behavior

• Document the reusability pattern for team members

• If you need more support, you can email us at snowconvert-support@snowflake.com