SnowConvert AI - SSIS Functional Differences

This section provides detailed documentation for all Functional Difference Messages (FDMs) that SnowConvert may generate during SSIS to dbt conversion. FDMs indicate where the converted code functions correctly but has behavioral differences from the original SSIS implementation.

For assistance with any FDM, you can use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions, or contact snowconvert-support@snowflake.com for additional support.

SSC-FDM-SSIS0001

Replace NULL with appropriate ORDER BY column(s) to ensure deterministic first match selection.

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.

Converted Code

WITH lookup_reference AS
(
   SELECT
      SalesTerritoryKey,
      SalesTerritoryAlternateKey,
      SalesTerritoryRegion,
      SalesTerritoryCountry,
      SalesTerritoryGroup,
      SalesTerritoryImage
   FROM
      {{ ref('stg_raw__lookup') }}
   QUALIFY
      ROW_NUMBER() OVER (
      PARTITION BY
         SalesTerritoryKey
      ORDER BY
         (
            SELECT
               --** SSC-FDM-SSIS0001 - REPLACE NULL WITH APPROPRIATE ORDER BY COLUMN(S) TO ENSURE DETERMINISTIC FIRST MATCH SELECTION. SSIS LOOKUP RETURNS THE FIRST MATCHING ROW, SO PROPER ORDERING IS REQUIRED WHEN MULTIPLE ROWS MATCH THE JOIN CONDITION. **
               null
         )) = 1
),
input_data AS
(
   SELECT
      EmployeeKey EmployeeKey,
      SalesTerritoryKey SalesTerritoryKey,
      BaseRate BaseRate,
      FirstName FirstName,
      LastName LastName
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   input_data.EmployeeKey,
   input_data.SalesTerritoryKey,
   input_data.BaseRate,
   input_data.FirstName,
   input_data.LastName,
   lookup_reference.SalesTerritoryRegion Region,
   lookup_reference.SalesTerritoryCountry Country
FROM
   input_data
   INNER JOIN
      lookup_reference
      ON lookup_reference.SalesTerritoryKey = input_data.SalesTerritoryKey
Copy

Best Practices

SSC-FDM-SSIS0002

Add an ORDER BY clause to ensure sorted output.

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.

Converted Code

--** 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
Copy

Best Practices

SSC-FDM-SSIS0003

The SSIS container was converted inline.

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.

Converted Code

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;
Copy

Best Practices

SSC-FDM-SSIS0004

Add an ORDER BY clause to ensure sorted output.

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.

Converted Code

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
Copy

Best Practices

SSC-FDM-SSIS0005

Package was converted to stored procedure because it is being reused by other packages.

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 at least one ExecutePackage task from another control flow. 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)

Converted Code

--** 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;
Copy

Best Practices

Language: English