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.

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

Best Practices¶

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

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

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

Best Practices¶

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

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

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;

Best Practices¶

• Review variable scope changes if the container had local variables

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

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

Best Practices¶

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

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

Best Practices¶

• Use CALL statements to invoke the procedure from parent packages

• Pass parameters explicitly in the CALL statement

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

Severity¶

None

Description¶

This FDM indicates that SSIS Send Mail Task SMTP connection settings were not converted. In SSIS, you configure custom SMTP server settings through an SMTP Connection Manager. In Snowflake, email delivery is managed entirely through the built-in Notification Integration service, and custom SMTP servers cannot be specified.

This is informational only and does not require action. Snowflake’s email service is reliable and properly configured.

Converted Code¶

BEGIN
   BEGIN
      LET my_package_Send_Mail_Task_integration_sql STRING := 'CREATE OR REPLACE NOTIFICATION INTEGRATION my_package_Send_Mail_Task
  TYPE=EMAIL
  ENABLED=TRUE
  ALLOWED_RECIPIENTS=("admin@example.com")';
      EXECUTE IMMEDIATE :my_package_Send_Mail_Task_integration_sql;
   END;
   --** SSC-FDM-SSIS0007 - CUSTOM SMTP SERVER SETTINGS ARE NOT APPLICABLE. SNOWFLAKE MANAGES EMAIL DELIVERY THROUGH THE NOTIFICATION INTEGRATION. **
   CALL SYSTEM$SEND_EMAIL('my_package_Send_Mail_Task', 'admin@example.com', 'Test', 'Test message');
END;

Best Practices¶

• No manual action required

• Snowflake handles email delivery through its managed infrastructure

• Ensure recipients are verified in your Snowflake account

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

Severity¶

None

Description¶

This FDM indicates that the SSIS Send Mail Task FROM address has been preserved by prepending it to the email body. Snowflake’s email service uses a fixed sender address managed by your Snowflake account and does not allow custom FROM addresses.

The original FROM address is included in the message body so recipients can see who intended to send the email.

Converted Code¶

BEGIN
   BEGIN
      LET my_package_Send_Mail_Task_integration_sql STRING := 'CREATE OR REPLACE NOTIFICATION INTEGRATION my_package_Send_Mail_Task
  TYPE=EMAIL
  ENABLED=TRUE
  ALLOWED_RECIPIENTS=("noreply@company.com", "admin@example.com")';
      EXECUTE IMMEDIATE :my_package_Send_Mail_Task_integration_sql;
   END;
   --** SSC-FDM-SSIS0008 - SNOWFLAKE'S EMAIL INTEGRATION USES A FIXED SENDER ADDRESS. THE ORIGINAL FROM ADDRESS HAS BEEN PREPENDED TO THE MESSAGE BODY FOR REFERENCE. **
   CALL SYSTEM$SEND_EMAIL('my_package_Send_Mail_Task', 'noreply@company.com,admin@example.com', 'Notification', 'Email sent by: noreply@company.com

Package completed successfully.');
END;

Best Practices¶

• No manual action required for basic functionality

• The FROM address is preserved in the message body for reference

• Consider updating email templates if the sender information format needs adjustment

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

Severity¶

None

Description¶

This FDM indicates that CC (carbon copy) recipients from the SSIS Send Mail Task have been merged into the main recipients list. Snowflake’s SYSTEM$SEND_EMAIL does not distinguish between TO and CC recipients. All recipients receive the email, but they will not see the CC distinction in their email client.

Converted Code¶

BEGIN
   BEGIN
      LET my_package_Send_Mail_Task_integration_sql STRING := 'CREATE OR REPLACE NOTIFICATION INTEGRATION my_package_Send_Mail_Task
  TYPE=EMAIL
  ENABLED=TRUE
  ALLOWED_RECIPIENTS=("admin@example.com", "team@example.com")';
      EXECUTE IMMEDIATE :my_package_Send_Mail_Task_integration_sql;
   END;
   --** SSC-FDM-SSIS0009 - SNOWFLAKE'S SYSTEM$SEND_EMAIL DOES NOT SUPPORT CC ADDRESSING. ALL CC RECIPIENTS HAVE BEEN ADDED TO THE MAIN RECIPIENTS LIST. **
   CALL SYSTEM$SEND_EMAIL('my_package_Send_Mail_Task', 'admin@example.com,team@example.com', 'Status Update', 'All systems operational.');
END;

Best Practices¶

• No manual action required for basic functionality

• All recipients will receive the email successfully

• If TO/CC distinction is important, consider adding recipient information in the email body

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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

Severity¶

None

Description¶

This FDM indicates that BCC (blind carbon copy) recipients from the SSIS Send Mail Task have been merged into the main recipients list. This is an important behavioral change: in SSIS, BCC recipients are hidden from other recipients. In Snowflake, all recipients are visible to each other because SYSTEM$SEND_EMAIL does not support BCC addressing.

Privacy concern: Recipients who were originally BCC’d will now be visible to all other recipients.

Converted Code¶

BEGIN
   BEGIN
      LET my_package_Send_Mail_Task_integration_sql STRING := 'CREATE OR REPLACE NOTIFICATION INTEGRATION my_package_Send_Mail_Task
  TYPE=EMAIL
  ENABLED=TRUE
  ALLOWED_RECIPIENTS=("admin@example.com", "audit@example.com")';
      EXECUTE IMMEDIATE :my_package_Send_Mail_Task_integration_sql;
   END;
   --** SSC-FDM-SSIS0010 - SNOWFLAKE'S SYSTEM$SEND_EMAIL DOES NOT SUPPORT BCC ADDRESSING. ALL BCC RECIPIENTS HAVE BEEN ADDED TO THE MAIN RECIPIENTS LIST, MAKING THEM VISIBLE TO ALL RECIPIENTS. **
   CALL SYSTEM$SEND_EMAIL('my_package_Send_Mail_Task', 'admin@example.com,audit@example.com', 'Audit Trail', 'Process completed.');
END;

Best Practices¶

• Review if BCC privacy is required for your use case

• If recipients must remain hidden, send separate emails to each BCC recipient

• Consider implementing a wrapper procedure that sends individual emails for BCC scenarios

• Use the SnowConvert Migration Assistant to get AI-powered explanations and actionable solutions for this FDM

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