SSIS - Snowflake Scripting output¶

This page describes the Snowflake Scripting output format for SSIS. In this format, each Data Flow Task is converted into a Snowflake stored procedure that the package’s Task graph calls. The result is plain Snowflake SQL that you can deploy with any SQL client. This is an alternative to the default dbt output format, where each Data Flow Task becomes a dbt project instead.

The Control Flow of a package converts the same way in both formats: tasks, containers, variables, Execute Package, event handlers, and file loops all become a Task graph. For those conversions, see the Control flow components in the SSIS overview. Only the Data Flow Task changes.

The procedure model¶

Each Data Flow Task becomes one Snowflake stored procedure in the public schema. The procedure is named after the Data Flow Task, with spaces replaced by underscores and the original casing kept, so Simple Data Flow becomes public.Simple_Data_Flow. This is the same identifier the dbt output uses for its project, so switching formats doesn’t rename the object. When two Data Flow Tasks in a package produce the same name, a numeric suffix such as Data_Flow_Task_1 keeps the names unique.

The procedure takes a single scope parameter, returns VARCHAR, and runs as the caller. The scope parameter carries the package name, so the procedure reads the same package variables that the Task graph set at runtime:

CREATE OR REPLACE PROCEDURE public.Simple_Data_Flow (scope VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      -- The Data Flow becomes the procedure body.
   END
$$;

The Task that runs the Data Flow sets the scope and calls the procedure:

CREATE OR REPLACE TASK public.completedataflow_complete_data_flow
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.completedataflow
AS
BEGIN
   LET scope VARCHAR := 'CompleteDataFlow';
   CALL public.Complete_Data_Flow(:scope);
END;

For comparison, the dbt output runs EXECUTE DBT PROJECT public.Complete_Data_Flow ARGS='build --target dev' in the same Task.

The conversion builds the procedure body in data-flow order. For the generated statements and complete examples, see Data flows and components:

1. If the Data Flow reads package variables, a LET declaration for each one reads its value with GetControlVariableUDF and the scope parameter.
2. Each source becomes a common table expression (CTE) named cte_<component>.
3. Each transformation becomes another CTE that reads the CTE of the component before it, in the order SSIS runs them.
4. The destination becomes an INSERT statement that carries the CTEs inside its source query.

Output shapes¶

The shape of the Data Flow decides what the conversion generates. The shape depends on how many writes the Data Flow performs and on whether a step has to be read by more than one statement:

A Snowflake CTE belongs to a single statement. A CTE chain is enough when the Data Flow is one pipeline with one write. When a step has to be read by a second write, the conversion persists it as a temporary table so that every later statement in the procedure can read it. This is the same CTE-promotion pattern that the Informatica PowerCenter Snowflake Scripting output uses, so customers who convert both tools see one stored-procedure model.

Supported components¶

Data Flow components that this format converts are listed below. Control Flow components aren’t listed because they convert the same way as in the dbt output. For the conversion of each Control Flow task and container, see the SSIS overview.

Components that aren’t listed, such as Character Map, Fuzzy Lookup, Script Component, Flat File Destination, and Copy Column, become a placeholder. See Unsupported components. Raw File Source, Raw File Destination, and XML Source aren’t available in either output format.

Unsupported components¶

A component that can’t be converted doesn’t stop the Data Flow and doesn’t send it back to dbt. The conversion emits a placeholder step that returns the expected columns as NULL, marked with SSC-EWI-SSIS0001, and still generates the rest of the procedure. The Task graph still calls the procedure, so the rest of the package stays reviewable and deployable. See Unsupported components for the generated code.

Deploying and running¶

In the Snowflake Scripting format, the Data Flow procedures are created directly in Snowflake, so there’s no dbt project to deploy before the Tasks run. Deploy the output in this order:

1. Run the etl_configuration/ files, which create the helper objects that the procedures and Tasks use: the variable procedures and functions, plus the file formats and stages for file loads.
2. Run each <DataFlowName>.sql file to create the Data Flow procedures.
3. Run each <PackageName>.sql file to create the Tasks.
4. Resume or run the root Task of the graph, exactly as you would with the dbt output.

To test a single Data Flow on its own, call its procedure with the package name as the scope: CALL public.Simple_Data_Flow('SimpleDataFlow');.

Before you run the procedures, replace any placeholder database and schema names, and land the files that file loads read. Flat file and Excel reads bind to the shared public.landing_stage the same way they do in the dbt output. For the ingestion manifest, the stage prefixes, and the Openflow flows that land the files, see Ingestion.

Reports¶

The conversion reports mark every Data Flow component that was converted in this format with the target element kind SNOWFLAKE_SCRIPT_STATEMENT, so you can see which parts of a package became Snowflake SQL. A component that produced a placeholder is reported as not supported with SSC-EWI-SSIS0001 and also appears in the issues report.

Shared references¶

Several references apply to both output formats, because the Control Flow conversion and the issue codes are the same regardless of format:

• SSIS overview: Control Flow tasks and containers, variables, ingestion, and the dbt output.
• Data flows and components: the generated code for each Data Flow component in this format.
• SSIS EWI codes and SSIS FDM codes: the issues the conversion can emit.