SQL Transformation Configuration
Use the Properties panel to configure the SQL transformation.
When you create an SQL transformation, you select a stored procedure or function that you want to run. The stored procedure or function must exist in the database before you select it in an SQL transformation. You map incoming fields to the input fields of the stored procedure or function, which the stored procedure or function uses as input parameters when it runs in the database. You can also set advanced properties for the transformation.
The following image shows the Properties panel of the SQL transformation:
The Properties panel of the SQL transformation includes the following tabs:
- •General. Defines the SQL transformation name and description.
- •SQL. Defines the database connection and the stored procedure or function to call from the transformation.
- •Incoming Fields. Defines field rules that determine the data to include in the transformation. Displays a preview of the SQL transformation fields.
- •Field Mapping. Maps incoming fields to the input fields of the stored procedure or function.
- •Output Fields. Displays a preview of the SQL transformation output fields.
- •Advanced. Defines advanced properties for the transformation such as tracing level and subsecond precision.
Note: Field name conflicts must be resolved in an upstream transformation. You cannot use field name conflict resolution rules in an SQL transformation.
Selecting a Stored Procedure or Function
Select the stored procedure or stored function to call from the SQL transformation on the SQL tab of the Properties panel. The stored procedure or function must exist in the database before you can select it for the transformation. You cannot parameterize a stored procedure or function.
Note: If you add a new stored procedure to the database while you have the Informatica Cloud mapping open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.
1. In the Properties panel of the SQL transformation, click the SQL tab.
2. Select the connection to the database. You must select a connection before you can select a stored procedure or function. If you want to parameterize the connection, you can do so after you select the stored procedure or function.
3. Select the SQL type. You can choose a stored procedure or stored function.
4. Enter the exact name of the stored procedure or function to call from the transformation. The stored procedure or function name is case-sensitive.
Or, click Select to select the stored procedure or function from the list.
5. Optionally, if you want to parameterize the connection, click New Parameter and enter the details for the connection parameter.
SQL Transformation Field Mapping
Configure field mapping in an SQL transformation to define how to use data from the upstream transformation in the stored procedure or function. Configure field mapping on the Field Mapping tab of the Properties panel.
You can configure the following field mapping options on the SQL transformation Field Mapping tab:
- Field Map Options
- Method of mapping fields to the SQL transformation. Select one of the following options:
- - Manual. Manually link incoming fields to the store procedure or function's input fields. Removes links for automatically mapped fields.
- - Automatic. Automatically link fields with the same name. Use when all of the fields that you want to link share the same name. You cannot manually link fields with this option.
- - Completely Parameterized. Use a parameter to represent the field mapping. In the task, you can configure all field mappings.
- - Partially Parameterized. Use a parameter to configure links in the mapping that you want to enforce, and display unmapped fields in the task. Or, use a parameter to configure links in the mapping, and allow all fields and links to display in the task for configuration.
- Show Fields
- Controls the fields that appear in the Incoming Fields list. Show all fields, unmapped fields, or mapped fields.
- Automatch
- Links fields with matching names. Allows you to link matching fields, and then manually configure other field mappings.
- Action menu
- Additional field link options. Provides the following options:
- - Map selected. Links the selected incoming field with the selected stored procedure or function input field.
- - Unmap selected. Clears the link for the selected field.
- - Clear all. Clears all field mappings.
- Show
- Determines how field names appear in the Stored Procedure Input Fields list. Use technical field names or labels.
SQL Transformation Output Fields
Output fields include output parameters from the database.
You can view output fields on the Output Fields tab of the Properties panel.
The Mapping Designer displays the name, type, precision, scale, and origin for each output field. You cannot edit the transformation output fields. If you want to exclude output fields from the data flow or rename output fields before you pass them to a downstream transformation, configure the field rules for the downstream transformation.
SQL Transformation Advanced Properties
Set advanced properties for the SQL transformation on the Advanced tab.
The following table describes the advanced properties:
Property | Description |
|---|
Tracing Level | Amount of transaction detail reported in the session log file. Use the following tracing levels: - - Terse
- - Normal
- - Verbose Initialization
- - Verbose Data
Default is Normal. |
Subsecond Precision | Specifies the subsecond precision for datetime fields. You can change the precision for databases that have an editable scale for datetime data. If you enable pushdown optimization, the database returns the complete datetime value, regardless of the subsecond precision setting. Enter a positive integer value from 0 to 9. Default is 6 microseconds. |
