SSIS¶

This section describes the dbt output format for SSIS: how Data Flow Tasks are converted into dbt projects and Control Flow tasks and containers into Snowflake orchestration. For a before/after example of each Data Flow component, see Mappings and transformations.

The default conversion path is a dbt project. Eligible Data Flow graphs that connect a Flat File Source to an OLE DB Destination emit Direct COPY and do not create a dbt project. The --SimplifySsisDataFlows flag is not part of this dbt documentation series.

How a Data Flow becomes a dbt project¶

Each Data Flow Task is converted into a standalone dbt project with a three-tier model architecture.

Each Data Flow produces this project structure:

{DataFlowName}/
├── dbt_project.yml                   # Materialization config and hooks
├── profiles.yml                      # Snowflake connection profile
├── models/
│   ├── sources.yml                   # Source table definitions
│   ├── staging/
│   │   └── stg_raw__*.sql
│   ├── intermediate/
│   │   └── int_*.sql
│   └── marts/
│       └── {destination}.sql
└── macros/
    └── *.sql                         # Unload helpers such as copy_into_stage

Most Data Flows follow the default path: the Data Flow becomes a dbt project, and the Control Flow Task that runs it calls EXECUTE DBT PROJECT. One case skips the dbt project. When a Data Flow only moves a Flat File Source into an OLE DB Destination, the conversion emits a Direct COPY instead: the Task runs a COPY INTO statement against the staged file, and no dbt project is generated for that Data Flow. Eligibility depends on the shape of the Data Flow, so check the generated output to see which path each Data Flow took. For the generated statements, see Mappings and transformations.

Ingestion¶

Generated flat-file reads bind to the shared public.landing_stage, and Flat File Destinations unload to it. Files consumed by the reads must be in the stage before the converted code runs. The conversion inventories each read, binds it to a stable stage prefix, and writes an ingestion manifest with importable Openflow flows that land the files.

Landing the files¶

1. Read the manifest: each source carries the bound stage prefix, the original locations found in the package, and the object-store prefix to upload to.
2. Map each original location to a prefix in your own object store: SnowConvert can’t infer a bucket from C:\Landing\Sales or from a UNC share, so that mapping is yours to decide. The stage side of the mapping is already fixed by the generated SQL.
3. Import the flow that matches your object store: every component is emitted disabled. The flow carries parameter names such as SOURCE_BUCKET, SOURCE_ENDPOINT, and SOURCE_REGION plus Snowflake connection settings, never values or credentials. Fill in the parameters and credentials service, then enable the flow. The canvas has one process group per package and a Shared group for project-level connection managers. A source that still needs a customer mapping, such as a connection string built from an SSIS expression, is omitted.
4. Confirm the files landed: LIST @public.landing_stage/ssis/ shows the objects that arrived under the generated prefixes.
5. Run the converted code: the Direct COPY tasks, dbt projects, and orchestration procedures all read from those prefixes. Destination keys stay the same on every run, so COPY INTO load history keeps working.

Stage prefixes¶

Each converted read binds to a prefix built from the SSIS identity that owns the file, not from the original folder:

A read of one exact file keeps the file name observed in the package, so a FinanceLoad package whose OrdersFile connection manager points at \\corp-fs\finance\incoming\orders.csv reads @public.landing_stage/ssis/FinanceLoad/OrdersFile/orders.csv. When the package doesn’t resolve to a file name, the path keeps UNKNOWN_FILE and no destination is invented for it.

ForEach File containers¶

A ForEach File container is the only shape where a file pattern, subfolder recursion, or a folder-only connection string is eligible. The land prefix carries the enumerated folder. FileSpec becomes the PATTERN, and TraverseSubfolders decides whether nested keys are preserved.

When the body is eligible, the loop collapses into a single patterned load: see ForEach File loads. A nested or otherwise ineligible body keeps the loop and falls back to LIST plus a cursor over the same bound prefix, as described in ForEach Loop Containers.

Sources that are inventoried¶

The manifest covers the reads that can bind to the landing stage:

• Flat File Sources with a delimited connection manager.
• Flat File Sources with a fixed-width connection manager when every column declares a width. The generated SQL slices those rows with SUBSTR.
• Excel Sources.

MultiFlatFile connection managers aren’t inventoried, and neither is a fixed-width connection manager whose column layout is incomplete.

Data flow components¶

These SSIS Data Flow sources, transformations, and destinations are supported. Unlisted Data Flow components generate EWI SSC-EWI-SSIS0001. Raw File Source, Raw File Destination, and XML Source are not yet available. For a before/after example of each, see Mappings and transformations.

Control flow components¶

These SSIS Control Flow tasks and containers are supported:

Note: Unlisted Control Flow elements generate EWI SSC-EWI-SSIS0004.

Container Conversion Details¶

Sequence, For Loop, and ForEach containers are converted inline within the parent task or procedure. Event Handlers are converted to stored procedures; supported scoped handlers can be wired into the parent orchestration with CALL.

Sequence Containers¶

Sequence containers 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:

:force:

-- 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 convert to Snowflake Scripting WHILE loops when InitExpression, EvalExpression, and AssignExpression are present.

Conversion behavior¶

Example¶

:force:

User_Counter := :User_MinValue;
WHILE (:User_Counter <= :User_MaxValue) LOOP
   -- Loop body tasks
   User_Counter := :User_Counter + 1;
END LOOP;

Limitations¶

• When Init, Eval, or Assign expressions are not present, the converted body runs once and SSC-EWI-SSIS0004 is generated.

ForEach Loop Containers¶

File Enumerator (Supported)

ForEach File Enumerator containers bind the enumerated folder to @public.landing_stage/ssis/<package>/<connection manager>/<enumerated folder>/. That bound prefix is the default. LIST @<STAGE_PLACEHOLDER> is not.

Eligible bodies that only move a Flat File Source into an OLE DB Destination collapse to Direct COPY. Patterned, recursive, retained-loop, and complete-fallback goldens live in ForEach File loads. A flat file connection manager whose connection string names a folder instead of a file can be bound only inside a ForEach File container, because the enumerator is what supplies the file names.

A container that can’t collapse keeps the loop. A nested container, a body that does more than the load, or a downstream task that reads the enumerated file name still reads the same bound prefix, this time with LIST and a cursor. A container whose folder can’t be resolved at all, such as one whose FileSpec comes from a property expression, falls back to LIST @<STAGE_PLACEHOLDER> and emits SSC-EWI-SSIS0014. Replace that placeholder with the stage that holds the folder before you run the task.

The files themselves reach the landing stage through the generated ingestion artifacts. See Ingestion.

ADO enumerator

ForEach ADO enumerators emit a cursor plus per-row variable assignments. The cursor source is a placeholder SELECT null until you replace it with the upstream result-set query. That placeholder emits SSC-EWI-SSIS0004. This is not a LIST pattern.

Conversion behavior¶

Example¶

:force:

LET ado_cursor CURSOR
FOR
   SELECT
      null;
!!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0004 - SSIS CONTROL FLOW ELEMENT 'FOREACH ADO ENUMERATOR - cursor source variable: User_ResultSetObject. Replace SELECT null with the query from the upstream Execute SQL Task that populates this variable.' CANNOT BE CONVERTED TO SNOWFLAKE SCRIPTING. ***/!!!
FOR ado_row IN ado_cursor DO
   User_ColumnA := :ado_row.User_ColumnA;
   CALL public.UpdateControlVariable('User_ColumnA', 'orders_package', TO_VARIANT(:User_ColumnA));
   User_ColumnB := :ado_row.User_ColumnB;
   CALL public.UpdateControlVariable('User_ColumnB', 'orders_package', TO_VARIANT(:User_ColumnB));
   -- Loop body tasks
END FOR;

Limitations¶

• SSC-EWI-SSIS0004. Replace SELECT null with the upstream result-set query before running the task.

From Variable enumerator

ForEach From Variable enumerators flatten a collection variable and iterate with RESULT_SCAN, not LIST.

Conversion behavior¶

Example¶

:force:

SELECT
   VALUE
FROM
   TABLE(FLATTEN(:User_MonthCollection));
LET from_var_cursor CURSOR
FOR
   SELECT
      VALUE
   FROM
      TABLE(RESULT_SCAN(LAST_QUERY_ID()));
FOR from_var_row IN from_var_cursor DO
   User_MonthValue := :from_var_row.VALUE;
   CALL public.UpdateControlVariable('User_MonthValue', 'orders_package', TO_VARIANT(:User_MonthValue));
   -- Loop body tasks
END FOR;

Other enumerator types

ForEach Item, NodeList, SMO, HDFS, and SchemaRowset enumerators are not yet available. An EWI (SSC-EWI-SSIS0004) is generated for these cases.

Event Handlers¶

Event handlers are converted to stored procedures. Supported scoped OnPreExecute and OnPostExecute handlers can be CALL-wired from the converted orchestration. Package-level handlers and OnError or OnWarning procedures can remain untriggered.

Conversion behavior¶

Example¶

:force:

--** SSC-FDM-SSIS0006 - EVENT HANDLER STORED PROCEDURE CREATED BUT NOT AUTOMATICALLY TRIGGERED. MANUAL INVOCATION OR TRIGGERING MECHANISM IMPLEMENTATION REQUIRED. **
CREATE OR REPLACE PROCEDURE public.orders_package_execute_sql_task_onerror_handler ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      INSERT INTO TaskErrorLog (TaskName, ErrorTime) VALUES ('Execute SQL Task', CURRENT_TIMESTAMP());
      RETURN 'SUCCESS';
   END;
$$;

CREATE OR REPLACE PROCEDURE public.orders_package_execute_sql_task_onpreexecute_handler ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      INSERT INTO TaskLog (TaskName, StartTime) VALUES ('Execute SQL Task', CURRENT_TIMESTAMP());
      RETURN 'SUCCESS';
   END;
$$;

CREATE OR REPLACE PROCEDURE public.orders_package_execute_sql_task_onpostexecute_handler ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      INSERT INTO TaskLog (TaskName, EndTime) VALUES ('Execute SQL Task', CURRENT_TIMESTAMP());
      RETURN 'SUCCESS';
   END;
$$;

CREATE OR REPLACE TASK public.orders_package_sequence_container
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.orders_package
AS
BEGIN
   CALL public.orders_package_execute_sql_task_onpreexecute_handler();
   UPDATE Employees
      SET Status = 'Active'
      WHERE Department = 'Sales';
   CALL public.orders_package_execute_sql_task_onpostexecute_handler();
END;

Limitations¶

• OnError, OnWarning, and package-level handler procedures can remain untriggered and require manual invocation or a triggering mechanism. See SSC-FDM-SSIS0006.

Execute SQL Task¶

Execute SQL Tasks are converted as inline SQL statements or separate stored procedures, depending on complexity and result set bindings.

Conversion approach:

• Simple SQL statements: Converted inline within the parent TASK
• Complex statements with result sets: May be converted to separate stored procedures
• Result bindings: Handled where possible; unsupported patterns generate EWI SSC-EWI-SSIS0011

Execute Package Task¶

Execute Package Tasks are handled differently based on package type:

Asynchronous execution note:

TASK-based Execute Package conversions run asynchronously. For synchronous behavior, packages are converted to stored procedures. See EWI SSC-EWI-SSIS0005.

File System Task¶

File System Tasks convert to Snowflake stage operations (COPY FILES and REMOVE). They do not become dbt models.

When the task acts on a file that a Flat File Source already binds, the generated statements use the same @public.landing_stage/ssis/<package>/ prefixes that the load reads, so the archive follows the loaded file rather than an unrelated path. See Ingestion.

Conversion behavior¶

Example¶

:force:

-- Copy
!!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0044 - THE COPY FILES COMMAND TREATS THE DESTINATION AS A DIRECTORY PREFIX AND APPENDS THE SOURCE FILENAME. IF THE DESTINATION PATH REFERS TO A SPECIFIC FILE RATHER THAN A DIRECTORY, THE RESULTING PATH MAY BE INCORRECT. REVIEW AND ADJUST MANUALLY IF NEEDED. ***/!!!
--** SSC-FDM-SSIS0025 - THE VARIABLE(S) VALUE(S) MUST CONTAIN A VALID SNOWFLAKE STAGE PATH. **
COPY FILES INTO :User_DestFilePath
FROM :User_SourceFilePath;

-- Move
COPY FILES INTO :User_DestFilePath
FROM :User_SourceFilePath;
REMOVE :User_SourceFilePath;

-- Delete directory content
--** SSC-FDM-SSIS0028 - SNOWFLAKE STAGES USE PREFIX-BASED PATHS, NOT REAL DIRECTORIES. THE REMOVE COMMAND WITH A TRAILING SLASH DELETES ALL FILES MATCHING THE PREFIX PATTERN. **
REMOVE :User_TempFolder;
EXECUTE IMMEDIATE 'COPY INTO ' || :User_TempFolder || '/.keep FROM (SELECT ''empty'') FILE_FORMAT = (TYPE = CSV COMPRESSION = NONE) OVERWRITE = TRUE SINGLE = TRUE';

Limitations¶

• Destination paths are directory prefixes (SSC-EWI-SSIS0044).
• Snowflake stages use prefix-based paths rather than real directories, so REMOVE with a trailing slash deletes every file matching the prefix (SSC-FDM-SSIS0028).
• Variable values must contain a valid Snowflake stage path (SSC-FDM-SSIS0025).

Expression Task¶

Expression Tasks convert SSIS expressions to Snowflake Scripting assignments (:=). They do not become dbt models.

Conversion behavior¶

Example¶

:force:

User_message := UPPER(:User_message || 'message');

Send Mail Task¶

Send Mail Tasks are converted to Snowflake Tasks that use SYSTEM$SEND_EMAIL with a dynamically created Notification Integration.

Conversion behavior¶

Property mapping¶

Example¶

Each Send Mail Task is converted to a Snowflake Task containing:

1. Notification Integration Creation: Created dynamically via EXECUTE IMMEDIATE
2. SYSTEM$SEND_EMAIL Call: Sends the email through the integration

:force:

CREATE OR REPLACE TASK public.my_package_send_mail_task
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.my_package
AS
BEGIN
   -- Step 1: Create Notification Integration dynamically
   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;

   -- Step 2: Send the email
   CALL SYSTEM$SEND_EMAIL('my_package_Send_Mail_Task', 'admin@example.com,team@example.com', 'Subject', 'Message body');
END;

Basic Email (To, Subject, Body):

:force:

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;
   CALL SYSTEM$SEND_EMAIL('my_package_Send_Mail_Task', 'admin@example.com', 'Daily Report', 'The daily report is ready.');
END;

Email with FROM Address:

:force:

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;

Email with Multiple Features (attachments, priority, CC):

:force:

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", "team@example.com")';
      EXECUTE IMMEDIATE :my_package_Send_Mail_Task_integration_sql;
   END;
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0015 - SNOWFLAKE'S SYSTEM$SEND_EMAIL DOES NOT SUPPORT FILE ATTACHMENTS. CONSIDER USING STAGED FILES WITH SHARED LINKS OR ALTERNATIVE DELIVERY METHODS. ***/!!!
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0016 - EMAIL PRIORITY SETTINGS (HIGH/NORMAL/LOW) ARE NOT SUPPORTED BY SYSTEM$SEND_EMAIL AND WILL BE IGNORED. ***/!!!
   --** 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. **
   --** 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', 'noreply@company.com,admin@example.com,team@example.com', 'Monthly Report', 'Email sent by: noreply@company.com

Please review the attached monthly report.');
END;

Prerequisites for Snowflake Email¶

Before using converted Send Mail Tasks:

1. Email Notification Integration permissions: Account admin must grant CREATE INTEGRATION ON ACCOUNT to the executing role
2. Recipient verification: All email addresses in ALLOWED_RECIPIENTS must be verified in Snowflake
3. Update warehouse name: Replace DUMMY_WAREHOUSE with your actual warehouse name

Limitations¶

File Attachments:

Upload files to a Snowflake stage and share links instead:

:force:

-- Upload file to stage
PUT file://report.pdf @my_stage;

-- Get shareable link (valid for 1 hour)
LET file_url STRING := GET_PRESIGNED_URL(@my_stage, 'report.pdf', 3600);

-- Include link in email body
CALL SYSTEM$SEND_EMAIL('my_integration', 'admin@example.com', 'Report Available',
  'Download the report from: ' || :file_url);

BCC Privacy:

Send separate emails to maintain recipient privacy:

:force:

-- Send to main recipients
CALL SYSTEM$SEND_EMAIL('my_integration', 'admin@example.com', 'Subject', 'Message');

-- Send separately to BCC recipients
CALL SYSTEM$SEND_EMAIL('my_integration', 'audit@example.com', 'Subject', 'Message');

Bulk Insert Task¶

Bulk Insert Tasks are converted to Snowflake Tasks that use COPY INTO with an inline FILE_FORMAT. The conversion generates a stage placeholder that you must configure before execution.

Conversion behavior¶

Property mapping¶

Terminator Parsing¶

SSIS uses specific tokens for field and row terminators. These are converted to Snowflake escape sequences:

Example¶

Each Bulk Insert Task is converted to a Snowflake Task containing a COPY INTO statement with an inline FILE_FORMAT:

:force:

CREATE OR REPLACE TASK public.package_bulk_insert_task
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.package
AS
BEGIN
   ---- Start block 'Package\BulkInsertTask'
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0024 - THE STAGE AND FILE UPLOAD ARE NOT INCLUDED IN THE TRANSLATION. CREATE A SNOWFLAKE STAGE AND UPLOAD THE SOURCE FILE BEFORE EXECUTING THE COPY INTO STATEMENT. REPLACE {STAGE_PLACEHOLDER} WITH YOUR STAGE NAME. ***/!!!
   COPY INTO target_table
   FROM '@{STAGE_PLACEHOLDER}'
   PATTERN = '.*data_file.*'
   FILE_FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ',', RECORD_DELIMITER = '\r\n', SKIP_HEADER = 1, NULL_IF = ('', 'NULL', 'null'), ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE)
   ON_ERROR = CONTINUE;
   ---- End block 'Package\BulkInsertTask'
END;

Basic Bulk Insert (CSV with default options):

:force:

CREATE OR REPLACE TASK public.package_load_customers
WAREHOUSE=DUMMY_WAREHOUSE
AS
BEGIN
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0024 - THE STAGE AND FILE UPLOAD ARE NOT INCLUDED IN THE TRANSLATION. CREATE A SNOWFLAKE STAGE AND UPLOAD THE SOURCE FILE BEFORE EXECUTING THE COPY INTO STATEMENT. REPLACE {STAGE_PLACEHOLDER} WITH YOUR STAGE NAME. ***/!!!
   COPY INTO Customers
   FROM '@{STAGE_PLACEHOLDER}'
   PATTERN = '.*customers\.csv.*'
   FILE_FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ',', RECORD_DELIMITER = '\r\n', SKIP_HEADER = 0, NULL_IF = ('', 'NULL', 'null'), ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE)
   ON_ERROR = CONTINUE;
END;

Bulk Insert with Tab Delimiter and Header Skip:

:force:

CREATE OR REPLACE TASK public.package_load_products
WAREHOUSE=DUMMY_WAREHOUSE
AS
BEGIN
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0024 - THE STAGE AND FILE UPLOAD ARE NOT INCLUDED IN THE TRANSLATION. CREATE A SNOWFLAKE STAGE AND UPLOAD THE SOURCE FILE BEFORE EXECUTING THE COPY INTO STATEMENT. REPLACE {STAGE_PLACEHOLDER} WITH YOUR STAGE NAME. ***/!!!
   COPY INTO Products
   FROM '@{STAGE_PLACEHOLDER}'
   PATTERN = '.*products\.txt.*'
   FILE_FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = '\t', RECORD_DELIMITER = '\n', SKIP_HEADER = 1, NULL_IF = ('', 'NULL', 'null'), ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE)
   ON_ERROR = SKIP_FILE_10;
END;

Bulk Insert with Multiple EWIs (Native format, LastRow, FireTriggers):

:force:

CREATE OR REPLACE TASK public.package_load_orders
WAREHOUSE=DUMMY_WAREHOUSE
AS
BEGIN
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0020 - SSIS BULKINSERTTASK NATIVE OR WIDENATIVE DATA FILE TYPE IS NOT SUPPORTED IN SNOWFLAKE. EXPORT SOURCE DATA TO CSV FORMAT BEFORE MIGRATION. ***/!!!
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0021 - SSIS BULKINSERTTASK LASTROW OPTION IS NOT SUPPORTED IN SNOWFLAKE. USE TEMPORARY TABLE WITH ROW_NUMBER AND LIMIT/OFFSET TO SELECT SPECIFIC ROW RANGE. ***/!!!
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0022 - SSIS BULKINSERTTASK FIRETRIGGERS OPTION IS NOT SUPPORTED IN SNOWFLAKE. CONSIDER USING SNOWFLAKE STREAMS AND TASKS TO IMPLEMENT TRIGGER-LIKE BEHAVIOR. ***/!!!
   !!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0024 - THE STAGE AND FILE UPLOAD ARE NOT INCLUDED IN THE TRANSLATION. CREATE A SNOWFLAKE STAGE AND UPLOAD THE SOURCE FILE BEFORE EXECUTING THE COPY INTO STATEMENT. REPLACE {STAGE_PLACEHOLDER} WITH YOUR STAGE NAME. ***/!!!
   COPY INTO Orders
   FROM '@{STAGE_PLACEHOLDER}'
   PATTERN = '.*orders\.dat.*'
   FILE_FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ',', RECORD_DELIMITER = '\r\n', SKIP_HEADER = 0, NULL_IF = ('', 'NULL', 'null'), ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE)
   ON_ERROR = CONTINUE;
END;

Stage Setup (Required)¶

Before executing converted Bulk Insert Tasks, you must:

1. Create a Snowflake stage:

:force:

CREATE OR REPLACE STAGE my_bulk_stage;

2. Upload files using SnowSQL CLI:

:force:

PUT file:///path/to/data.csv @my_bulk_stage AUTO_COMPRESS = FALSE;

3. Replace the stage placeholder in generated code:

:force:

-- Change this:
FROM '@{STAGE_PLACEHOLDER}'

-- To this:
FROM '@my_bulk_stage'

4. Verify files are staged:

:force:

LIST @my_bulk_stage;

Limitations¶

Native Data Format:

Export SQL Server data to CSV format before migration. The native binary format is not supported by Snowflake.

LastRow Filtering:

Load to staging table and filter:

:force:

-- Load all data
COPY INTO staging_table FROM '@my_stage' ...;

-- Insert only rows up to LastRow value
INSERT INTO target_table
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY 1) AS rn
  FROM staging_table
) WHERE rn <= 1000;  -- Original LastRow value

FireTriggers (Trigger-like Behavior):

Use Snowflake Streams and Tasks:

:force:

-- Create stream to capture inserts
CREATE OR REPLACE STREAM target_stream ON TABLE target_table;

-- Create task to process inserts (trigger logic)
CREATE OR REPLACE TASK process_inserts
  WAREHOUSE = my_warehouse
  SCHEDULE = '1 minute'
  WHEN SYSTEM$STREAM_HAS_DATA('target_stream')
AS
  INSERT INTO audit_table
  SELECT *, CURRENT_TIMESTAMP()
  FROM target_stream
  WHERE METADATA$ACTION = 'INSERT';

dbt Project Execution¶

Within the orchestration code, Data Flow Tasks are executed using Snowflake’s EXECUTE DBT PROJECT command:

:force:

EXECUTE DBT PROJECT schema.project_name ARGS='build --target dev'

Important requirements:

• The project_name must match the name you used when deploying the dbt project (via CREATE DBT PROJECT or Snowflake Workspace deployment)
• Arguments passed are standard dbt CLI arguments (like build, run, test)
• Each execution runs the entire dbt project with all models in dependency order

Deployment:

Before executing dbt projects in orchestration, deploy them using:

• Snowflake CLI: snow dbt deploy --schema schema_name --database database_name package_name
• Snowflake Workspace: Upload and deploy via UI