SnowConvert AI - SSIS Conversion Issues¶

Severity¶

Critical

Description¶

This EWI is added when an SSIS component cannot be automatically converted to Snowflake SQL or dbt. The component is not supported by SnowConvert’s conversion engine and requires manual implementation. This typically occurs with custom components, third-party transformations, or components that have no direct equivalent in Snowflake’s architecture.

The conversion will place a placeholder comment in the generated code indicating where manual intervention is required.

Example Code¶

<component
  refId="Package\Data Flow Task\ScriptComponent1"
  componentClassID="Microsoft.ScriptComponentHost"
  name="ScriptComponent1">
  <properties>
    <property dataType="System.String" name="ScriptLanguage">VisualBasic</property>
  </properties>
</component>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0001 - SSIS COMPONENT IS NOT SUPPORTED BY SNOWCONVERT ***/!!!
-- Component: ScriptComponent1 requires manual implementation

Best Practices¶

• Review the original SSIS component’s logic and data transformation requirements

• Implement equivalent functionality using Snowflake SQL, dbt models, or Snowflake stored procedures

• For script components, convert the VB.NET/C# code to SQL or Python (using Snowpark)

• Consider using dbt custom macros for reusable transformation logic

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

Severity¶

High

Description¶

This EWI is generated when an SSIS expression contains syntax that cannot be automatically translated to Snowflake SQL. This commonly occurs with:

• Complex nested expressions with unsupported functions

• SSIS-specific functions without direct Snowflake equivalents

• Malformed expressions (e.g., unbalanced parentheses)

• Expressions using unsupported operators or type conversions

The generated code will include a placeholder where the expression should be manually translated.

Example Code¶

<outputColumn name="nc1" dataType="i4">
  <properties>
    <property name="Expression">(1 + 1))</property>
    <property name="FriendlyExpression">(1 + 1))</property>
  </properties>
</outputColumn>

WITH source_data AS
(
   SELECT
      ID,
      NAME
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0002 - SSIS EXPRESSION CANNOT BE CONVERTED TO SNOWFLAKE SQL: (1 + 1)) ***/!!!
   _ AS nc1,
   ID AS ID,
   NAME AS NAME
FROM
   source_data

Best Practices¶

• Carefully review the original SSIS expression logic

• Manually translate the expression to valid Snowflake SQL syntax

• Test the converted expression thoroughly with sample data

• Use Snowflake’s built-in functions as equivalents where possible

• For complex calculations, consider breaking into multiple steps for clarity

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

Severity¶

High

Description¶

This EWI is added when SQL statements embedded in SSIS components (such as OLE DB Source, Lookup, or Execute SQL Task) cannot be automatically converted to Snowflake syntax. This typically occurs when:

• The source SQL dialect has syntax incompatible with Snowflake

• The SQL contains system-specific functions or objects

• The SQL uses features not supported in Snowflake

Example Code¶

<component componentClassID="Microsoft.Lookup" name="Lookup">
  <properties>
    <property name="SqlCommand">
      SELECT CustomerID, CustomerName 
      FROM DimCustomer 
      WHERE CONVERT(VARCHAR, LastModified, 101) > '01/01/2020'
    </property>
  </properties>
</component>

--** SSC-EWI-SSIS0003 - EMBEDDED SQL CANNOT BE CONVERTED FROM SQL SERVER TO SNOWFLAKE SQL **
-- Original SQL: SELECT CustomerID, CustomerName FROM DimCustomer 
--                WHERE CONVERT(VARCHAR, LastModified, 101) > '01/01/2020'

Best Practices¶

• Identify the source SQL dialect (SQL Server, Oracle, etc.)

• Use Snowflake’s equivalent functions (e.g., TO_VARCHAR instead of CONVERT)

• Update date/time formatting to use Snowflake’s format strings

• Test the converted SQL against your Snowflake schema

• Review Snowflake documentation for function equivalents

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

Severity¶

High

Description¶

This EWI is generated when an SSIS control flow element cannot be converted to Snowflake scripting. This can occur with various unsupported control flow tasks and containers, including but not limited to:

Common Scenarios:

• Control flow task types not yet supported by SnowConvert

• Container iteration logic that cannot be directly translated

• Complex control flow patterns without Snowflake equivalents

• Control flow elements with configurations that cannot be mapped to Snowflake

The specific control flow element that triggered this EWI will be identified in the error message, and manual implementation is required using Snowflake’s procedural SQL constructs.

Common Cases¶

<DTS:Executable DTS:refId="Package\For Loop Container"
  DTS:CreationName="STOCK:FORLOOP"
  DTS:ObjectName="For Loop Container">
  <DTS:Properties>
    <DTS:Property DTS:Name="InitExpression">@Counter = 1</DTS:Property>
    <DTS:Property DTS:Name="EvalExpression">@Counter &lt;= 10</DTS:Property>
    <DTS:Property DTS:Name="AssignExpression">@Counter = @Counter + 1</DTS:Property>
  </DTS:Properties>
</DTS:Executable>

CREATE OR REPLACE TASK simpleforloop_package_for_loop_container
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.simpleforloop_package_execute_sql_task
AS
!!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0004 - SSIS CONTROL FLOW ELEMENT 'FORLOOP CONTAINER ITERATION LOGIC' CANNOT BE CONVERTED TO SNOWFLAKE SCRIPTING. ***/!!!
BEGIN
   -- Loop body tasks here
END;

<DTS:Executable DTS:CreationName="STOCK:FOREACHLOOP"
  DTS:ObjectName="Foreach Loop Container">
  <DTS:ForEachEnumerator>
    <!-- ADO enumerator, NodeList enumerator, etc. -->
    <DTS:Property DTS:Name="EnumerationType">3</DTS:Property>
  </DTS:ForEachEnumerator>
</DTS:Executable>

!!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0004 - SSIS CONTROL FLOW ELEMENT 'FOREACH CONTAINER ITERATION LOGIC' CANNOT BE CONVERTED TO SNOWFLAKE SCRIPTING. ***/!!!
FOR record IN cursor DO
   -- Loop body tasks here
END FOR;

General Best Practices¶

• Review the specific control flow element mentioned in the EWI message

• Understand the original SSIS logic and data processing requirements

• Implement equivalent functionality using Snowflake’s procedural SQL

• Use appropriate Snowflake constructs (WHILE, FOR, CURSOR, IF/ELSE, etc.)

• Test thoroughly with representative data and scenarios

• Document any behavioral differences from the original SSIS implementation

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

Severity¶

High

Description¶

This EWI indicates that an SSIS Execute Package Task has been converted to a Snowflake TASK, which executes asynchronously by default. In SSIS, Execute Package Task runs synchronously within the parent package’s execution context. In Snowflake, EXECUTE TASK triggers the task to run asynchronously, which may affect orchestration logic, error handling, and variable passing between packages.

If synchronous execution is required, consider converting the called package to a stored procedure and using CALL instead.

Example Code¶

<DTS:Executable DTS:CreationName="Microsoft.ExecutePackageTask"
  DTS:ObjectName="Execute Package Task">
  <DTS:ObjectData>
    <ExecutePackageTask>
      <UseProjectReference>true</UseProjectReference>
      <PackageName>ChildPackage.dtsx</PackageName>
    </ExecutePackageTask>
  </DTS:ObjectData>
</DTS:Executable>

CREATE OR REPLACE TASK parent_package_execute_package_task
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.parent_package_previous_task
AS
EXECUTE TASK public.childpackage_child_package;
-- !!!SSC-EWI-SSIS0005 - THIS TASK RUNS ASYNCHRONOUSLY. Original SSIS Execute Package Task ran synchronously.

Best Practices¶

• Verify that asynchronous execution doesn’t break orchestration dependencies

• For synchronous behavior, convert child packages to stored procedures

• Implement error handling and logging for asynchronous task execution

• Review variable passing mechanisms between parent and child

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

Severity¶

High

Description¶

This EWI is generated when an Execute Package Task contains variable bindings (parameter mappings between parent and child packages) that could not be automatically converted. SSIS allows parent packages to pass variable values to child packages through parameter bindings. This mechanism requires manual implementation in Snowflake using one of these approaches:

• Pass values through stored procedure parameters

• Use a control table to share values between tasks

• Leverage Snowflake session variables

Example Code¶

<ExecutePackageTask>
  <PackageName>ChildPackage.dtsx</PackageName>
  <ParameterAssignments>
    <ParameterAssignment>
      <ParameterName>ChildParam</ParameterName>
      <VariableName>User::ParentVariable</VariableName>
    </ParameterAssignment>
  </ParameterAssignments>
</ExecutePackageTask>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0006 - SSIS EXECUTE PACKAGE TASK VARIABLE BINDINGS WERE NOT CONVERTED. ***/!!!
-- Original binding: ParentVariable -> ChildParam
-- Implement parameter passing mechanism manually
EXECUTE TASK public.childpackage;

Best Practices¶

• Document all variable mappings between parent and child packages

• Convert child packages to stored procedures with explicit parameters

• Use the control_variables table pattern for shared state

• Implement validation for required parameters

• Test parameter passing thoroughly

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

Severity¶

High

Description¶

This EWI is generated when an SSIS executable (task or container) uses property expressions to dynamically set properties at runtime, and these expressions could not be converted. Property expressions in SSIS allow dynamic configuration of task properties using expressions based on variables or parameters. In Snowflake, similar dynamic behavior must be implemented using:

• Snowflake scripting variables

• Dynamic SQL construction

• Conditional logic in stored procedures

Example Code¶

<DTS:Executable DTS:refId="Package\Execute SQL Task"
  DTS:CreationName="Microsoft.ExecuteSQLTask">
  <DTS:PropertyExpression DTS:Name="SqlStatementSource">
    @[User::SqlQuery]
  </DTS:PropertyExpression>
</DTS:Executable>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0007 - SSIS EXECUTABLE CONTAINS PROPERTY EXPRESSIONS that were not converted. ***/!!!
-- Original property expression: SqlStatementSource = @[User::SqlQuery]
-- Implement dynamic SQL logic manually

Best Practices¶

• Identify which properties were dynamically configured

• Implement equivalent logic using Snowflake variables and string concatenation

• Use EXECUTE IMMEDIATE for dynamic SQL execution

• Validate dynamically constructed SQL to prevent injection risks

• Test with all possible variable value scenarios

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

Severity¶

Medium

Description¶

This EWI indicates that an Execute Package Task references a package that exists outside the current project or conversion scope. The external package must be:

• Included in the conversion input

• Converted separately and deployed to Snowflake

• Referenced correctly in the generated Snowflake code

Ensure all dependent packages are available and properly referenced in your Snowflake environment.

Example Code¶

<ExecutePackageTask>
  <UseProjectReference>false</UseProjectReference>
  <PackageNameFromProjectReference></PackageNameFromProjectReference>
  <PackageLocation>File System</PackageLocation>
  <PackageName>C:\ExternalPackages\UtilityPackage.dtsx</PackageName>
</ExecutePackageTask>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0008 - EXECUTE PACKAGE TASK REFERENCES AN EXTERNAL PACKAGE ***/!!!
-- External package: C:\ExternalPackages\UtilityPackage.dtsx
-- Ensure this package is converted and accessible
EXECUTE TASK public.utilitypackage;

Best Practices¶

• Create an inventory of all external package dependencies

• Include external packages in the conversion scope

• Ensure external packages are deployed to Snowflake before dependent packages

• Update task references to match deployed Snowflake object names

• Document package dependencies in your migration documentation

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

Severity¶

None

Description¶

This EWI indicates that an unexpected error occurred during the conversion of a specific component. This is typically a rare occurrence and may be caused by:

• Corrupted package metadata

• Unusual component configuration

• Edge cases not covered by the converter

The component may have been partially converted. Review the generated code and contact support if the issue persists.

Example Code¶

<!-- Corrupted or unusual component configuration -->

-- !!!SSC-EWI-SSIS0009 - UNEXPECTED EXCEPTION CONVERTING COMPONENT. ***/!!!
-- Review component configuration and generated code

Best Practices¶

• Review the original SSIS package for any unusual configurations

• Check for package file corruption

• Verify package compatibility with SSIS versions supported by SnowConvert

• Contact support with package details if issue persists

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

Severity¶

None

Description¶

This EWI is generated when multiple SSIS components write to the same destination table, resulting in multiple dbt models targeting the same table. This typically occurs when:

• Multiple Data Flow Tasks write to the same table

• Different packages in the same conversion write to the same table

• The same table is used as a destination in multiple transformations

Review your data flow design and consolidate models if appropriate, or ensure the models run in the correct sequence.

Example Code¶

<!-- Package 1 -->
<component componentClassID="Microsoft.OLEDBDestination" name="OLE DB Destination 1">
  <properties>
    <property name="OpenRowset">[dbo].[FactSales]</property>
  </properties>
</component>

<!-- Package 2 -->
<component componentClassID="Microsoft.OLEDBDestination" name="OLE DB Destination 2">
  <properties>
    <property name="OpenRowset">[dbo].[FactSales]</property>
  </properties>
</component>

-- Model 1: models/factsales.sql
-- Model 2: models/factsales_1.sql (automatically renamed)
-- !!!SSC-EWI-SSIS0010 - A model associated with table 'FactSales' is already defined. ***/!!!

Best Practices¶

• Review duplicate destination references in your packages

• Consolidate multiple writes to the same table into a single model if possible

• Use dbt’s incremental models for append/update patterns

• Ensure models targeting the same table run in the correct dependency order

• Document the intended behavior when multiple models write to the same table

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

Severity¶

High

Description¶

This EWI is generated when an Execute SQL Task has a result binding configured (to capture query results into a variable), but the SQL statement is not a SELECT query. Result bindings only work with SELECT statements that return result sets. If the SQL statement is an INSERT, UPDATE, DELETE, or procedural statement, the result binding cannot be applied and must be manually addressed.

For non-query statements, consider using OUTPUT parameters or separate SELECT statements to retrieve values.

Example Code¶

<SQLTask:SqlTaskData SQLTask:SqlStatementSource="DELETE FROM Customers WHERE CustomerId = 1">
  <SQLTask:ResultBinding
    SQLTask:ResultName="RowCount"
    SQLTask:DtsVariableName="User::DeletedRows" />
</SQLTask:SqlTaskData>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0011 - RESULT BINDING IS CONFIGURED FOR NON-QUERY STATEMENT. RESULT BINDING ONLY WORKS WITH SELECT QUERIES. ***/!!!
DELETE FROM Customers WHERE CustomerId = 1;
-- Original result binding: RowCount -> User::DeletedRows

Best Practices¶

• Change non-query statements to SELECT queries where possible

• Use separate SELECT statements to retrieve values after DML operations

• For row counts, use SELECT CHANGES() or similar Snowflake functions

• Review the purpose of the result binding and implement equivalent logic

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

Severity¶

High

Description¶

This EWI is generated when an Execute SQL Task is configured to return results as XML, which is not directly supported in the conversion. SnowConvert supports SINGLEROW and FULLRESULTSET result types, but XML result sets require manual implementation. You will need to:

• Modify the SQL to return results in a format Snowflake can process

• Implement XML generation using Snowflake’s XML functions if needed

• Consider alternative data formats like JSON (which Snowflake supports natively)

Example Code¶

<SQLTask:SqlTaskData 
  SQLTask:SqlStatementSource="SELECT * FROM Customers FOR XML AUTO"
  SQLTask:ResultType="ResultSetType_XML">
</SQLTask:SqlTaskData>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0012 - XML RESULT SET TYPE IS NOT SUPPORTED. ONLY SINGLEROW AND FULLRESULTSET RESULT SET TYPES ARE SUPPORTED. ***/!!!
-- Original SQL: SELECT * FROM Customers FOR XML AUTO
-- Convert to supported result type or use JSON format

Best Practices¶

• Replace XML output with JSON using Snowflake’s OBJECT_CONSTRUCT or ARRAY_AGG

• Remove FOR XML clauses and return structured data

• If XML is required, generate it in downstream processing

• Update any code that consumes the XML to work with the new format

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

Severity¶

High

Description¶

This EWI is generated when a property expression in an SSIS task contains patterns that cannot be automatically converted. SnowConvert supports simple property expressions such as:

• Single variable references: @[User::VariableName]

• Simple string concatenation: "SELECT * FROM " + @[User::TableName]

More complex patterns involving multiple operations, nested expressions, or complex string manipulation require manual conversion.

Example Code¶

<DTS:PropertyExpression DTS:Name="SqlStatementSource">
  "SELECT * FROM " + (LEN(@[User::Schema]) > 0 ? @[User::Schema] + "." : "") + @[User::TableName]
</DTS:PropertyExpression>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0013 - PROPERTY EXPRESSION FOR SQLSTATEMENTSOURCE CONTAINS UNSUPPORTED PATTERNS. ONLY SINGLE VARIABLE REFERENCES OR SIMPLE STRING CONCATENATION WITH LITERALS AND VARIABLE REFERENCES IS SUPPORTED. ***/!!!
-- Implement complex expression logic manually

Best Practices¶

• Break complex expressions into multiple steps using Snowflake variables

• Use Snowflake’s string functions (CONCAT, IFF, etc.) for dynamic construction

• Implement conditional logic using CASE statements or IF blocks

• Test dynamic SQL thoroughly with various input scenarios

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

Severity¶

High

Description¶

This EWI is generated when a ForEach File Enumerator Container is used to iterate over files in a folder. In SSIS, this references local or network file system paths. In Snowflake, files must be staged in Snowflake internal or external stages. You must:

• Map the folder path to a Snowflake stage

• Replace the <STAGE_PLACEHOLDER> with your actual stage name

• Ensure files are uploaded to the stage before execution

• Implement the file enumeration logic using Snowflake’s LIST command

Example Code¶

<DTS:Executable DTS:CreationName="STOCK:FOREACHLOOP"
  DTS:ObjectName="Foreach Loop Container">
  <DTS:ForEachFileEnumerator>
    <DTS:Property DTS:Name="Directory">C:\Data\InputFiles\</DTS:Property>
    <DTS:Property DTS:Name="FileSpec">*.csv</DTS:Property>
  </DTS:ForEachFileEnumerator>
</DTS:Executable>

-- !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0014 - THE FOLDER PATH REQUIRES MANUAL MAPPING TO A SNOWFLAKE STAGE. ***/!!!
-- Original folder: C:\Data\InputFiles\
-- Replace <STAGE_PLACEHOLDER> with your stage name
-- Example: @my_stage/input_files/
DECLARE
   file_cursor CURSOR FOR 
      SELECT relative_path 
      FROM TABLE(RESULT_SCAN(
         SELECT system$list_files('<STAGE_PLACEHOLDER>')
      ))
      WHERE relative_path LIKE '%.csv';

Best Practices¶

• Create a Snowflake stage for file storage

• Upload files to the stage using SnowSQL, Snowpipe, or cloud storage integration

• Update the stage reference in the generated code

• Test file enumeration with actual staged files

• Use Snowflake’s LIST command to verify files are accessible

• Document stage naming conventions for your project

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