Configuring a Generic SQL Transformation Task

Define a generic SQL transformation script using the Task Group component.

1. Add the Task Group component to the design window, right-click the component on the design window, and select Add task to create a new transformation task. Alternatively, double-click an existing Task Group component or with a Task Group component selected in the design window, click the Task icon in the Properties window, and click the Create a New Task icon.
2. Select Generic SQL as the task type. Enter a name and description for the task and click Create.
   The Generic SQL Transformation Editor appears, which includes the Transformation Definition, Dictionary, and Transformation panes.
   • Dictionary – displays all the tables and views on the Model tab, and the recently used tables or views on the Recent tab. You can set the default number of tables or views that you want to view under the Recent tab, in the File > Preferences window under Query Designer.
   • Transformation Definition – includes the Design window, which is used to automatically generate SQL statements that are specific to the records you are working with. The Design window contains these tabbed panes to display different parts of the SQL transformation:

     • Tables – define source tables and joins.

     • Joins – defines join operations and the join ordering.

     • Attributes – defines attributes and their transformation.

     • Filters – defines the WHERE condition.

     • Sort – defines the SORT BY condition.

     • Group – defines the GROUP BY condition.

     • Target – determines the kind of SQL statement to be generated, like INSERT, UPDATE, DELETE operations, defines the destination table and the attribute mappings.

       Note: Source attributes are mapped to target attributes based on their order in the Attributes tab. You may need to change the order of attributes in the Attributes tab to match the order specified in the Target tab. This ordering also affects any target attributes used in key joins between the source query and the destination table.
   • Transformation – set source query attribute usage, view the attribute details, as well as the generated transformation at any point of the script creation.