SSIS - Mappings and transformations¶

This page shows how each SSIS Data Flow component is converted into a dbt model, with a before and after example for each. For the supported-component matrix and the Control Flow deep-dives, see the SSIS overview.

A Data Flow Task becomes a dbt project. Within that project, each component becomes a SQL model: source reads become stg_ staging models, transformations become int_ models, and destinations become models named after the target table. The examples below are taken from the test suite.

Sources and staging¶

OLE DB Source¶

An OLE DB Source becomes a staging model that reads from a table declared in sources.yml.

Conversion behavior¶

The staging model is named stg_raw__<component>_<table> and selects every column that the component exposes, aliasing each one to its output name. The table itself isn’t hardcoded: the model reads through {{ source('raw', '<table>') }}, and a generated sources.yml lists the tables that the package reads.

Example¶

sources.yml:

version: 2
sources:
  - name: raw
    schema: YOUR_SCHEMA
    database: YOUR_DB
    tables:
      - name: DimCustomer

Snowflake (stg_raw__ole_db_source_dimcustomer.sql):

SELECT
    CustomerKey AS CustomerKey,
    GeographyKey AS GeographyKey,
    CustomerAlternateKey AS CustomerAlternateKey,
    Title AS Title,
    FirstName AS FirstName,
    MiddleName AS MiddleName,
    LastName AS LastName,
    NameStyle AS NameStyle,
    BirthDate AS BirthDate,
    MaritalStatus AS MaritalStatus,
    Suffix AS Suffix,
    Gender AS Gender,
    EmailAddress AS EmailAddress,
    YearlyIncome AS YearlyIncome,
    TotalChildren AS TotalChildren,
    NumberChildrenAtHome AS NumberChildrenAtHome,
    EnglishEducation AS EnglishEducation,
    SpanishEducation AS SpanishEducation,
    FrenchEducation AS FrenchEducation,
    EnglishOccupation AS EnglishOccupation,
    SpanishOccupation AS SpanishOccupation,
    FrenchOccupation AS FrenchOccupation,
    HouseOwnerFlag AS HouseOwnerFlag,
    NumberCarsOwned AS NumberCarsOwned,
    AddressLine1 AS AddressLine1,
    AddressLine2 AS AddressLine2,
    Phone AS Phone,
    DateFirstPurchase AS DateFirstPurchase,
    CommuteDistance AS CommuteDistance
FROM
    {{ source('raw', 'DimCustomer') }}

Limitations¶

sources.yml is generated with YOUR_DB and YOUR_SCHEMA placeholders. Replace them with your Snowflake database and schema before you run the project.

Excel Source¶

An Excel Source becomes a staging model that reads the worksheet through the excel_source_udf Python UDF.

Conversion behavior¶

The workbook is bound to the landing stage, and an excel_raw_data CTE reads it with TABLE(excel_source_udf('<stage path>', '<worksheet>', '<HDR flag>')). A parsed_data CTE then types each column: text columns are cast with :: VARCHAR, and numeric, date, timestamp, time, and Boolean columns use the matching TRY_TO_ function, so a value that can’t be parsed becomes null instead of failing the load. The original Excel connection string is kept as a comment at the top of the model. When the connection sets HDR=NO, the worksheet has no header row and the columns are named F1, F2, and so on.

Example¶

Snowflake (stg_raw__excel_source.sql):

--Original Excel path: Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\data\sales_data.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";
WITH excel_raw_data AS
(
   SELECT
      data
   FROM
      TABLE(excel_source_udf('@public.landing_stage/ssis/Package/sales_data.xlsx', 'Sales', 'YES'))
),
parsed_data AS
(
   SELECT
      data['ProductName'] :: VARCHAR AS ProductName,
      TRY_TO_DOUBLE(data['Quantity'] :: VARCHAR) AS Quantity,
      TRY_TO_DOUBLE(data['UnitPrice'] :: VARCHAR) AS UnitPrice,
      TRY_TO_DOUBLE(data['TotalAmount'] :: VARCHAR) AS TotalAmount
   FROM
      excel_raw_data
)
SELECT
   *
FROM
   parsed_data

Limitations¶

An error output that uses the RedirectRow disposition can’t be translated directly. The model still reads the worksheet the same way, but it’s flagged with SSC-EWI-SSIS0028 and the component is reported as Partial in the assessment report. Use TRY_TO_* functions with error flag columns for defensive error handling, and create separate error-capture models if needed.

ADO.NET Source¶

An ADO.NET Source becomes a staging model in the same way an OLE DB Source does.

Conversion behavior¶

In table or view access mode, the model selects every column that the component exposes, aliases each one to its output name, and reads the table through {{ source('raw', '<table>') }}. The schema qualifier in the component’s table or view name is dropped from the source reference, because the schema comes from sources.yml.

Example¶

Snowflake (stg_raw__ado_net_source.sql), for a component that reads "dbo"."Departments":

SELECT
   dept_name AS dept_name,
   id AS id
FROM
   {{ source('raw', 'Departments') }}

Oracle Source¶

An Oracle Source becomes a staging model that reads the Oracle table through a dbt source.

Conversion behavior¶

In table access mode, the component’s table name resolves the source table, the schema qualifier is dropped from the source reference, and each column is aliased to its output name. The Oracle SQL command access mode is normalized to the OLE DB SQL command mode, so the query itself is converted as embedded SQL rather than as part of the component.

Example¶

Snowflake (stg_raw__oracle_source.sql), for a component that reads "HR"."DEPARTMENTS":

SELECT
   dept_id AS dept_id,
   dept_name AS dept_name
FROM
   {{ source('raw', 'DEPARTMENTS') }}

Limitations¶

When an Oracle Source uses a SQL command and that query can’t be converted to Snowflake SQL, the staging model keeps the original statement as a comment and is flagged with SSC-EWI-SSIS0003:

!!!RESOLVE EWI!!! /*** SSC-EWI-SSIS0003 - EMBEDDED SQL CANNOT BE CONVERTED FROM ETL TO SNOWFLAKE SQL ***/!!!
--SELECT employee_id, full_name, department FROM hr.employees WHERE active = 1
SELECT
   *
FROM
   DUAL

Row-level transformations¶

Derived Column¶

A Derived Column becomes an int_ model whose SELECT list carries one expression per derived column.

Conversion behavior¶

The input rows are wrapped in a source_data CTE that reads from the upstream model with {{ ref() }}. Each SSIS expression becomes its Snowflake equivalent in the SELECT list: string concatenation stays ||, and date parts become the matching Snowflake function. Columns that pass through unchanged are selected alongside the derived ones, so downstream components still see the full row.

Example¶

Snowflake (int_derived_column.sql):

WITH source_data AS
(
   SELECT
      FirstName,
      MiddleName,
      LastName,
      BirthDate
   FROM
      {{ ref('stg_raw__ole_db_source_dimcustomer') }}
)
SELECT
   FirstName || ' ' || MiddleName || ' ' || LastName AS FullName,
   YEAR(BirthDate) AS BirthDateYear,
   FirstName AS FirstName,
   MiddleName AS MiddleName,
   LastName AS LastName,
   BirthDate AS BirthDate
FROM
   source_data

Data Convert¶

A Data Convert becomes an int_ model that casts each converted column to the target type.

Conversion behavior¶

Each conversion becomes a :: cast aliased to the component’s output column name. When the component adds a converted copy instead of replacing the column, the original column is kept and the cast column is emitted next to it under its SSIS output name, such as "Copy of BirthDate".

Example¶

Snowflake (int_data_conversion.sql, column list shortened):

WITH source_data AS
(
   SELECT
      GeographyKey,
      BirthDate,
      TotalChildren,
      CustomerKey
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   GeographyKey,
   BirthDate,
   TotalChildren,
   CustomerKey,
   GeographyKey :: BINARY(50) AS "Copy of GeographyKey",
   BirthDate :: DATE AS "Copy of BirthDate",
   TotalChildren :: NUMERIC(18, 2) AS "Copy of TotalChildren"
FROM
   source_data AS sd

Character Map¶

A Character Map becomes an int_ model that applies supported character operations to string columns and passes other columns through unchanged.

Conversion behavior¶

Uppercase and lowercase operations become the Snowflake UPPER and LOWER functions. The generated model reads the upstream model through a source_data CTE and applies each operation in the SELECT list. Operations can replace a column in place or create a new output column.

Example¶

Snowflake (int_character_map.sql):

WITH source_data AS
(
   SELECT
      FullName,
      Score
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   UPPER(FullName) AS FullName,
   Score
FROM
   source_data

Limitations¶

Only uppercase and lowercase operations are translated. For unsupported map flags, such as byte reversal, the column passes through unchanged and the generated model includes SSC-EWI-SSIS0019.

OLE DB Command¶

An OLE DB Command becomes an incremental model that applies the component’s parameterized DELETE or UPDATE to the table that the command names.

Conversion behavior¶

The model is named after the target table and is generated under models/marts/. Its config block sets materialized='incremental' and an incremental strategy that matches the command: delete_only for a DELETE and update_only for an UPDATE. The WHERE clause parameters become the unique_key, as a single value for one key column and as an array for a composite key, and a schema-qualified command also sets schema. Each model is tagged oledb_command plus delete_operation or update_operation.

A DELETE model selects only the key columns. An UPDATE model also selects the SET columns and lists them in merge_update_columns. The conversion adds the matching strategy macro to the project, get_incremental_delete_only_sql or get_incremental_update_only_sql, which runs a MERGE INTO that ends in WHEN MATCHED THEN DELETE or WHEN MATCHED THEN UPDATE SET.

Example¶

SSIS (the component’s SQL command):

DELETE FROM dbo.Contacts WHERE ContactID = ?

With ContactID mapped to that parameter, the conversion generates models/marts/Contacts.sql. The model’s config block sets materialized='incremental', incremental_strategy='delete_only', unique_key='ContactID', and schema='dbo', the model carries the oledb_command and delete_operation tags, and its SELECT returns only ContactID.

Combining data¶

Lookup¶

A Lookup becomes an int_ model that joins the input rows to a deduplicated lookup source.

Conversion behavior¶

The generated model has two CTEs: lookup_reference reads the reference model and keeps one row per lookup key with QUALIFY ROW_NUMBER() = 1, and input_data reads the upstream model. The two are combined with an INNER JOIN on the lookup condition, and the returned lookup columns are appended to the input columns.

When the lookup key can contain nulls, the join uses EQUAL_NULL so that null keys match the way they do in SSIS.

Chained lookups become one model each. The second Lookup reads the first one’s model with {{ ref('int_lookup') }} rather than re-reading the source.

Example¶

Snowflake (int_lookup.sql), a lookup with no deterministic ordering:

WITH lookup_reference AS
    (
        SELECT
            CustomerKey ,
            FirstName
        FROM
            {{ ref('stg_raw__lookup') }}
    QUALIFY
    ROW_NUMBER() OVER (
    PARTITION BY
    CustomerKey
    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
    ProductKey ,
    OrderDateKey ,
    CustomerKey ,
    OrderQuantity ,
    TotalPrice ,
    OrderDate
FROM
    {{ ref('stg_raw__ole_db_source') }}
    )
SELECT
    input_data.ProductKey,
    input_data.OrderDateKey,
    input_data.CustomerKey,
    input_data.OrderQuantity,
    input_data.TotalPrice,
    input_data.OrderDate,
    lookup_reference.FirstName CustomerName
FROM
    input_data
        INNER JOIN
    lookup_reference
    ON lookup_reference.CustomerKey = input_data.CustomerKey

Snowflake (int_lookup_1.sql), the second Lookup in the same Data Flow, with sort columns available and a null-safe join:

WITH lookup_reference AS
    (
        SELECT
            DateKey ,
            CalendarYear
        FROM
            {{ ref('stg_raw__lookup_1') }}
    QUALIFY
    ROW_NUMBER() OVER (
    PARTITION BY
    DateKey
    ORDER BY DateKey, FullDateAlternateKey) = 1
    ),
    input_data AS
   (
SELECT
    CustomerName ,
    ProductKey ,
    OrderDateKey ,
    CustomerKey ,
    OrderQuantity ,
    TotalPrice ,
    OrderDate
FROM
    {{ ref('int_lookup') }}
    )
SELECT
    input_data.CustomerName,
    input_data.ProductKey,
    input_data.OrderDateKey,
    input_data.CustomerKey,
    input_data.OrderQuantity,
    input_data.TotalPrice,
    input_data.OrderDate,
    lookup_reference.CalendarYear
FROM
    input_data
        INNER JOIN
    lookup_reference
    ON EQUAL_NULL(lookup_reference.DateKey, input_data.OrderDateKey)

Limitations¶

An SSIS Lookup returns the first matching row. When the conversion can’t determine a deterministic order for the lookup source, it emits SSC-FDM-SSIS0001 and leaves null in the ORDER BY. Replace that null with the columns that make the match deterministic, otherwise the row that Snowflake keeps can vary between runs.

Fuzzy Lookup¶

A Fuzzy Lookup becomes an int_ model that matches rows by string similarity instead of by equality.

Conversion behavior¶

The generated model has two CTEs: lookup_reference reads the reference model, and input_data reads the upstream model. The two are combined with a CROSS JOIN, and each fuzzy-matched column pair is scored with JAROWINKLER_SIMILARITY divided by 100.0. A WHERE clause keeps only the pairs that meet the component’s minimum similarity, and QUALIFY ROW_NUMBER() <= 1, partitioned by the input columns and ordered by the similarity score, keeps the best match for each input row.

The model returns the passthrough input columns, the reference columns that the component copies, a _Similarity and a _Confidence column, and one _Similarity_<column> column per fuzzy-matched column. Copied reference columns keep the output names from the SSIS component. In the example below, the copied CompanyName column is named RefCompanyName because that is the name in the package, not because the conversion adds a prefix.

Example¶

Snowflake (int_fuzzy_lookup.sql):

--** SSC-FDM-SSIS0024 - THE SSIS FUZZYLOOKUP TRANSFORMATION USES A TOKEN-BASED SIMILARITY ALGORITHM. THE CONVERTED SQL USES JAROWINKLER_SIMILARITY WHICH IS A CHARACTER-LEVEL ALGORITHM. SIMILARITY SCORES MAY DIFFER BETWEEN SOURCE AND TARGET. _CONFIDENCE IS APPROXIMATED AS _SIMILARITY (NO EQUIVALENT RELATIVE CONFIDENCE METRIC IN SNOWFLAKE). UNMATCHED INPUT ROWS ARE EXCLUDED — SSIS PRESERVES ALL INPUT ROWS WITH SIMILARITY=0 AND NULL REFERENCE COLUMNS. **
WITH lookup_reference AS
(
   SELECT
      RefID ,
      CompanyName ,
      Industry
   FROM
      {{ ref('stg_raw__fuzzy_lookup') }}
),
input_data AS
(
   SELECT
      InputID InputID,
      CompanyName CompanyName,
      City City
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   input_data.CompanyName,
   lookup_reference.CompanyName RefCompanyName,
   lookup_reference.Industry,
   JAROWINKLER_SIMILARITY(input_data.CompanyName, lookup_reference.CompanyName) / 100.0 AS _Similarity,
   JAROWINKLER_SIMILARITY(input_data.CompanyName, lookup_reference.CompanyName) / 100.0 AS _Confidence,
   JAROWINKLER_SIMILARITY(input_data.CompanyName, lookup_reference.CompanyName) / 100.0 AS _Similarity_CompanyName
FROM
   input_data
   CROSS JOIN lookup_reference
WHERE
   JAROWINKLER_SIMILARITY(input_data.CompanyName, lookup_reference.CompanyName) / 100.0 >= 0.3
QUALIFY
   ROW_NUMBER() OVER (
   PARTITION BY
      input_data.InputID, input_data.CompanyName, input_data.City
   ORDER BY
      JAROWINKLER_SIMILARITY(input_data.CompanyName, lookup_reference.CompanyName) DESC) <= 1

The reference table is read through its own staging model (stg_raw__fuzzy_lookup.sql) and is listed in the generated sources.yml.

Limitations¶

Every converted Fuzzy Lookup emits SSC-FDM-SSIS0024, because the two matching algorithms don’t behave identically. SSIS uses token-based similarity, while JAROWINKLER_SIMILARITY is character-level, so scores can differ between source and target. _Confidence is approximated as _Similarity, since Snowflake has no equivalent relative confidence metric. Unmatched input rows are excluded from the output, while SSIS keeps them with a similarity of 0 and null reference columns.

Union All¶

A Union All becomes an int_ model that combines its inputs with UNION ALL.

Conversion behavior¶

Each input becomes its own CTE (input_1, input_2, and so on) that reads one upstream model and renames its columns to the Union All output names. The CTEs are then combined with UNION ALL in input order, so columns line up even when the sources name them differently.

Example¶

Snowflake (int_union_all.sql):

WITH input_1 AS
(
   SELECT
      CustomerKey Key,
      FirstName Name
   FROM
      {{ ref('stg_raw__ole_db_source') }}
),
input_2 AS
(
   SELECT
      FirstName Name,
      EmployeeKey Key
   FROM
      {{ ref('stg_raw__ole_db_source_1') }}
),
input_3 AS
(
   SELECT
      ResellerKey Key,
      ResellerName Name
   FROM
      {{ ref('stg_raw__ole_db_source_2') }}
)
SELECT
   input_1.Key Key,
   input_1.Name Name
FROM
   input_1
UNION ALL
SELECT
   input_2.Key Key,
   input_2.Name Name
FROM
   input_2
UNION ALL
SELECT
   input_3.Key Key,
   input_3.Name Name
FROM
   input_3

Routing and normalizing¶

Cache¶

A Cache Transform becomes a passthrough int_ model.

Conversion behavior¶

In SSIS, the Cache Transform writes its input to a Cache connection manager so that downstream Lookups can read it, and passes the same rows through unchanged. The generated model reproduces the passthrough part: it selects every input column from the upstream model, without renaming or reordering. Cache file persistence and cache index columns don’t appear in the generated SQL, because dbt materializations handle persistence and a downstream Lookup reads the model with its own query.

Property mapping¶

Example¶

Snowflake (int_cache_transform.sql):

SELECT
   name,
   salary
FROM
   {{ ref('stg_raw__ole_db_source') }}

Limitations¶

When the component sets TreatDuplicateKeysAsError to true, SSIS fails the package on a duplicate cache key. The generated model doesn’t enforce that check, so duplicate rows pass through unchanged and the model emits SSC-FDM-SSIS0027. The SQL runs correctly as a passthrough. If you need the validation, add a dbt uniqueness test or a Snowflake unique constraint.

Aggregation and ranking¶

Aggregate¶

An Aggregate becomes an int_ model that groups rows and applies aggregate functions.

Conversion behavior¶

Group-by columns appear in both the SELECT and GROUP BY clauses. Aggregate output columns become the corresponding Snowflake functions: COUNT, COUNT(DISTINCT ...), SUM, AVG, MIN, or MAX. When no group-by columns are configured, the functions aggregate the entire input. When the component contains only group-by columns, the generated query groups by those columns without applying aggregate functions.

Property mapping¶

Example¶

Snowflake (int_aggregate.sql):

WITH source_data AS
(
   SELECT
      department,
      salary
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   department,
   SUM(salary) AS total_salary
FROM
   source_data
GROUP BY
   department

Pivot¶

A Pivot becomes an int_ model that converts row values into columns by using conditional aggregation.

Conversion behavior¶

Set-key and passthrough columns appear in the SELECT and GROUP BY clauses. Each declared pivot-key value becomes an output column that uses MAX(CASE WHEN ... THEN ... END). The Pivot always generates an intermediate model.

Property mapping¶

Example¶

Snowflake (int_pivot.sql):

--** SSC-FDM-SSIS0022 - THE SSIS PIVOT TRANSFORMATION ASSUMES INPUT DATA IS SORTED BY THE SET KEY COLUMN. THE CONVERTED SQL USES GROUP BY WHICH DOES NOT REQUIRE OR PRESERVE SORT ORDER. VERIFY THAT DOWNSTREAM CONSUMERS DO NOT DEPEND ON SORTED OUTPUT. **
WITH source_data AS
(
   SELECT
      CustomerName,
      Product,
      Quantity
   FROM
      {{ ref('stg_raw__source') }}
)
SELECT
   CustomerName,
   MAX(CASE
      WHEN Product = 'Bike'
         THEN Quantity
   END) AS Bike,
   MAX(CASE
      WHEN Product = 'Helmet'
         THEN Quantity
   END) AS Helmet,
   MAX(CASE
      WHEN Product = 'Gloves'
         THEN Quantity
   END) AS Gloves
FROM
   source_data
GROUP BY
   CustomerName

Limitations¶

SSIS expects Pivot input to be sorted by the set key. The generated Snowflake query uses GROUP BY, which doesn’t require or preserve that order, so it emits SSC-FDM-SSIS0022. Verify that downstream consumers don’t depend on sorted output.

UnPivot¶

An UnPivot becomes an int_ model that converts input columns into rows.

Conversion behavior¶

When all unpivoted columns map to one destination value column, the model uses Snowflake UNPIVOT. When they map to multiple destination columns, the model generates one filtered SELECT per pivot-key value and combines them with UNION ALL. Passthrough columns remain unchanged. An additional UNION preserves the SSIS behavior for input rows in which all unpivoted values are null.

Property mapping¶

Example¶

Snowflake (int_unpivot.sql):

WITH source_data AS
(
   SELECT
      Ham,
      Milk,
      Soda,
      Chips,
      customer,
      description
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   Sales,
   customer,
   description,
   q1 :: NUMERIC AS q1
FROM
   source_data UNPIVOT (q1 FOR Sales IN (Ham, Milk, Soda, Chips))
UNION
SELECT
   null AS Sales,
   customer,
   description,
   null AS q1
FROM
   source_data
WHERE
   Ham IS NULL
   AND Milk IS NULL
   AND Soda IS NULL
   AND Chips IS NULL

Sorting¶

Sort¶

A Sort becomes an int_ model that orders the rows and keeps one row per sort key.

Conversion behavior¶

The input rows are wrapped in a source_data CTE. The model then applies QUALIFY ROW_NUMBER() = 1 partitioned by the sort columns, which reproduces the Sort component’s removal of rows with duplicate sort values, and an ORDER BY that carries each sort column and its direction.

Example¶

Snowflake (int_sort.sql):

WITH source_data AS
(
   SELECT
      name,
      salary
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   name,
   salary
FROM
   source_data
QUALIFY
   ROW_NUMBER()
   OVER (
   PARTITION BY (
      name)
   ORDER BY
      name ASC) = 1
ORDER BY
   name ASC

Keys and load strategy¶

Row Count¶

A Row Count becomes a pass-through int_ model that records the row count in the package variable.

Conversion behavior¶

The model is materialized as a view and selects every input column unchanged, so the component doesn’t alter the data flow. The count itself is captured by a pre_hook that calls the m_update_row_count_variable macro with the SSIS variable name, the relation to count, and the variable scope.

Example¶

Snowflake (int_data_flow_task_row_count.sql, column list shortened):

{{ config(
    materialized='view',
    pre_hook="""{{ m_update_row_count_variable(
        variable_name='User_Variable',
        target_relation=ref('stg_raw__ole_db_source'),
        variable_scope='Package'
    ) }}"""
) }}
WITH source_data AS
(
   SELECT
      CustomerKey,
      GeographyKey,
      CustomerAlternateKey,
      Title
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
    sd.CustomerKey,
    sd.GeographyKey,
    sd.CustomerAlternateKey,
    sd.Title
FROM
    source_data AS sd

Targets¶

OLE DB Destination¶

An OLE DB Destination becomes the model that writes the Data Flow’s output to the destination table.

Conversion behavior¶

The model is named after the destination component and carries a config(alias=...) that points at the destination table, so the table keeps its original name even when the component doesn’t. The model reads the last upstream model, aliases each column to the destination column name, and applies any cast that the column mapping requires.

Example¶

Snowflake (ole_db_destination.sql):

{{ config(
    alias='newDimCustomer'
) }}
WITH source_data AS
(
   SELECT
      CustomerKey,
      AddressLine1,
      AddressLine2,
      FullName,
      BirthDateYear
   FROM
      {{ ref('stg_raw__ole_db_source_dimcustomer') }}
)
SELECT
    sd.CustomerKey AS CustomerKey,
    sd.AddressLine1 AS AddressLine1,
    sd.AddressLine2 AS AddressLine2,
    sd.FullName AS FullName,
    sd.BirthDateYear :: VARCHAR(50) AS BirthDateString
FROM
    source_data AS sd

Excel Destination¶

An Excel Destination becomes the model that writes the Data Flow’s output, using the worksheet name as the model alias.

Conversion behavior¶

The model follows the same pattern as an OLE DB Destination: it reads the last upstream model in a source_data CTE and aliases each column to the destination column name. The config(alias=...) value is the worksheet name with its trailing $, surrounding quotes, and brackets removed, so Sheet1$ becomes Sheet1 and 'Sales Data$' becomes Sales Data. When the component doesn’t name a worksheet, the model is generated without an alias.

Example¶

Snowflake (excel_destination.sql):

{{ config(
    alias='Sheet1'
) }}
WITH source_data AS
(
   SELECT
      ProductName,
      Quantity,
      UnitPrice
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   sd.ProductName AS ProductName,
   sd.Quantity AS Quantity,
   sd.UnitPrice AS UnitPrice
FROM
   source_data AS sd

Oracle Destination¶

An Oracle Destination becomes the model that writes the Data Flow’s output to the Oracle target table.

Conversion behavior¶

An Oracle Destination has no access mode property, so the conversion resolves the schema and table from the component’s table name. The table name becomes the model’s config(alias=...), and the model reads the last upstream model in a source_data CTE and aliases each column to the destination column name.

Example¶

Snowflake (oracle_destination.sql), for a component that writes to "HR"."EMPLOYEES":

{{ config(
    alias='EMPLOYEES'
) }}
WITH source_data AS
(
   SELECT
      emp_id,
      emp_name
   FROM
      {{ ref('stg_raw__ole_db_source') }}
)
SELECT
   sd.emp_id AS emp_id,
   sd.emp_name AS emp_name
FROM
   source_data AS sd

Direct copy loads¶

Direct COPY¶

A Data Flow that only moves a Flat File Source into an OLE DB Destination becomes a direct COPY INTO load instead of a dbt project.

Conversion behavior¶

This is the default for eligible Flat File Source to OLE DB Destination graphs. The Data Flow becomes a Snowflake task that runs one COPY INTO statement, and no dbt project is generated for that Data Flow. The statement lists the destination columns, reads the staged file positionally as $1, $2, and so on, and applies the casts that the column mapping requires. The flat file connection manager becomes a named file format that carries the field delimiter, the number of header lines to skip, and null handling, and the file itself is read from the shared landing stage. The --SimplifySsisDataFlows conversion option is outside the scope of this page, and a Data Flow that isn’t eligible for a direct load falls back to the dbt project conversion described earlier on this page.

Example¶

Snowflake (DirectFlatFileLoad.sql):

CREATE OR REPLACE TASK public.directflatfileload
AS
SELECT
   1;
CREATE OR REPLACE TASK public.directflatfileload_data_flow_task
WAREHOUSE=DUMMY_WAREHOUSE
AFTER public.directflatfileload
AS
BEGIN
   ---- Start block 'Package\Data Flow Task'
   COPY INTO sales.customer_orders (
customer_id,
customer_name
)
   FROM
   (
      SELECT
         $1 :: NUMERIC,
         $2 :: VARCHAR(50) :: VARCHAR(20)
      FROM
         @public.landing_stage/ssis/DirectFlatFileLoad/OrdersFlatFile/input.csv (FILE_FORMAT => 'DirectFlatFileLoad_Data_Flow_Task_Orders_Source')
   );
   ---- End block 'Package\Data Flow Task'

END;

Snowflake (DirectFlatFileLoad/file_formats.sql):

CREATE FILE FORMAT IF NOT EXISTS DirectFlatFileLoad_Data_Flow_Task_Orders_Source
TYPE = 'CSV'
FIELD_DELIMITER = ','
SKIP_HEADER = 1
EMPTY_FIELD_AS_NULL = TRUE;

Snowflake (stages.sql):

-- SnowConvert land zone: every converted flat-file read and unload binds to this stage.
CREATE STAGE IF NOT EXISTS public.landing_stage
  COMMENT = 'SnowConvert-generated land zone. Retarget URL/integration; keep the name and subfolder layout.';

Limitations¶

Every converted flat-file read binds to the generated public.landing_stage. Retarget its URL or storage integration for your account, and keep the stage name and the subfolder layout that the COPY INTO statements expect.