ETL+ Design Page

Controls how data is loaded into the target SQL Server based data warehouse. Allows data types to be changed and T-SQL expressions applied to the data loaded into the target table.

This page allows the definition of logical keys (Key+ used for delta loads), column data types (VarChar, Dw Data Type) and transformations (T-SQL Expression).

1. Source - Table Name: Design page for Source - Table Name.

2. Scr PK. Keys designated in the data source.

3. Key+. Check boxes for the columns that define the logical keys used to process this target/data warehouse table. Currently, this is only used for Upsert delta data extraction processes and extraction of large OData tables.
   

   

4. Column Name. Table’s column names.

5. Source Data Type.

6. VarChar. This forces the target data type to be varchar(max). This is helpful when the source data might cause SQL errors because SQL can’t accept some of the source’s values. For instance, a numeric source field has some rows with characters, or a source date field has invalid dates for SQL (click here to see SQL valid date ranges).

7. DW Data Type. Allows the configuration of data warehouse data types:

   • Type in a new Dw Data Type, click Enter to accept.

   • Use a row’s dropdown list to pick your choice.

   • Depending on the source data type and what you change on this dropdown, ETL+ will automatically create a T-SQL Expression to cover the data (see the next step). Feel free to edit the expression.

8. T-SQL Expression: Enter MS SQL Expressions to transform the column’s data.
   E.g.; LEFT(ColumnName, 5).
   This transformation is applied after the raw data is loaded to the data warehouse.

9. Column names in bold: Their Dw Data Type has changed from the source’s data type. Ex above: City comes as a varchar(max) from OData, and the user changed it to varchar(50).

10. Confirm. Confirm changes currently done on this page.

11. Cancel. Cancel changes currently done on this page.

Upper Right

12. ETL+ Build
    - Version number: Format vYYYY.MM.DDBB. E.g; v2021.08.2703 means the 3rd build on 08/27/2021.
    - 32 or 64 bits.

13. ? Help View this page’s knowledge page help.

Bottom left

14. User: your ETL+ username currently logged to ETL+.

15. Entity: the Entity ID, Data Warehouse name connected to this ETL+ session.
    Example: abc,aa_dw3 refers to Entity: abc, Data warehouse name: aa_dw3.

Related Pages

• ETL+ Design Page

• ETL+ Extract SQL Statement Panel

• Design page Key+ designation for Load Upsert ETL+ Load Upsert

• Design page Key+ designation for OData ETL+ Source Driver for OData

v2023.07 ✅