SQL transformation configuration

General

Configure the SQL transformation name and optional description.

Incoming Fields

Define field rules that determine the data to include in the transformation.

Not applicable to unconnected SQL transformations.

SQL

Define the database connection and the type of SQL that the transformation processes: either a stored procedure, stored function, or query.

If you configure the transformation to process a stored procedure, stored function, or saved query, you select the stored procedure, stored function, or saved query on this tab.

If you configure the transformation to process a stored procedure, you can choose to run the transformation as an unconnected transformation.

If you configure the transformation to process a user-entered query, the SQL editor appears on this tab. Enter the query in the SQL editor.

Input Fields

For transformations that process stored procedures, displays the stored procedure input fields.

Field Mapping

For stored procedure and stored functions, specify how to map incoming fields to the input fields of the selected stored procedure or function.

You do not configure the field mapping for queries or unconnected SQL transformations.

Output Fields

For stored procedures, stored functions, and saved queries, displays a preview of the SQL transformation output fields. For user-entered queries, configure output fields for the columns retrieved from the database.

For queries, the output fields also include the SQLError field, the optional NumRowsAffected field, and optional pass-through fields.

Advanced

Define advanced properties for the transformation. Advanced properties differ based on the type of SQL that the transformation processes.

Configuring the SQL type

Selecting a stored procedure or function

1In the Properties panel of the SQL transformation, click the SQL tab.
The following image shows the SQL tab when the SQL type is set to Stored Procedure:

2Select the connection to the database.
You can select the connection or use a parameter.
Note: If you want to parameterize the connection, create the parameter after you select the stored procedure or function.
3Set the SQL Type to Stored Procedure or Stored Function.
4Click Select to select the stored procedure or function from the database, or enter the exact name of the stored procedure or function to call.
The stored procedure or function name is case-sensitive.
Note: If you add a new stored procedure to the database while you have the 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.
5If the transformation processes a stored procedure, and you want to run the transformation in unconnected mode, select Unconnected Stored Procedure.
Note: This option is not available for all connector types. For more information, see the help for the appropriate connector.
6If you want to parameterize the connection, click New Parameter and enter the details for the connection parameter.

Selecting a saved query

1In the Properties panel of the SQL transformation, click the SQL tab.
2Select the connection to the database.
You can select the connection or use a parameter.
3Set the SQL type to SQL Query.
4Set the query type to Saved Query.
5Click Select to select the saved query from a list of Data Integration assets.

Entering a query

1In the Properties panel of the SQL transformation, click the SQL tab.
2Select the connection to the database or use a parameter.
3Set the SQL type to SQL Query.
4Set the query type to Entered Query.
5If you do not want to parameterize the query, enter the query in the query editor.
Incoming fields are listed on the Fields tab. To add a field to the query, select the field and click Add.
You can format the SQL and validate the syntax.
Note: The syntax validation performs a general SQL syntax check but does not verify the SQL against the database. The validation can return a syntax error even though the SQL is valid for the database. In this case, you can still save and run the mapping.
If you update the incoming fields after you configure the query, open the SQL tab to refresh the changes.
6If you want to parameterize the query, perform the following steps:
1. aOpen the Parameters tab and create a new string parameter.
2. bSelect the parameter, and then click Add to add the parameter to the query editor.
When you add the parameter to the query editor, Data Integration encloses it in dollar sign characters ($).
Do not format the SQL or validate the query.

SQL transformation field mapping

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. Configure links in the mapping that you want to enforce and use a parameter to allow other fields to be mapped in the mapping task. Or, use a parameter to configure links in the mapping, and allow all fields and links to display in the task for configuration.
To see more information on field mapping parameters, see Mappings.

Show Fields

Controls the fields that appear in the Incoming Fields list. Show all fields, unmapped fields, or mapped fields.

Automap

Links fields with matching names. Allows you to link matching fields, and then manually configure other field mappings.

You can map fields in the following ways:

• - Exact Field Name. Data Integration matches fields of the same name.
• - Smart Map. Data Integration matches fields with similar names. For example, if you have an incoming field Cust_Name and a target field Customer_Name, Data Integration automatically links the Cust_Name field with the Customer_Name field.

You can use both Exact Field Name and Smart Map in the same field mapping. For example, use Exact Field Name to match fields with the same name and then use Smart Map to map fields with similar names.

You can undo all automapped field mappings by clicking Automap > Undo Automap. To unmap a single field, select the field to unmap and click Actions > Unmap.

Data Integration highlights newly mapped fields. For example, when you use Exact Field Name, Data Integration highlights the mapped fields. If you then use Smart Map, Data Integration only highlights the fields mapped using Smart Map.

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 for stored procedures and functions

Output fields for queries

Retrieved column fields

When the SQL query contains a SELECT statement, the transformation returns one row for each database row that it retrieves.

For user-entered queries, you must configure an output field for each column in the SELECT statement. The output fields must be in the same order as the columns in the SELECT statement.

For saved queries, Data Integration creates the output fields.

SQLError field

Data Integration returns row errors to the SQLError field when it encounters a connection or syntax error. It returns NULL to the SQLError field when no SQL errors occur.

For example, the following SQL query generates a row error from an Oracle database when the Employees table does not contain Product_ID:

SELECT Product_ID FROM Employees

Data Integration generates one row. The SQLError field contains the following error text in one line:

ORA-0094: “Product_ID”: invalid identifier Database driver error... Function Name: Execute SQL Stmt: SELECT Product_ID from Employees Oracle Fatal Error

When a query contains multiple statements, and you configure the SQL transformation to continue on SQL error, the SQL transformation might return rows from the database for one query statement, but return database errors for another query statement. The SQL transformation returns any database error in a separate row.

NumRowsAffected field

You can enable the NumRowsAffected output field to return the number of rows affected by the INSERT, UPDATE, or DELETE query statements in each input row. Data Integration returns the NumRowsAffected for each statement in the query. NumRowsAffected is disabled by default.

When you enable NumRowsAffected and the SQL query does not contain an INSERT, UPDATE, or DELETE statement, NumRowsAffected is zero in each output row.

The following table lists the output rows that the SQL transformation generates when you enable NumRowsAffected:

Query Statement	Output Rows
UPDATE, INSERT, DELETE only	One row for each statement with the NumRowsAffected for the statement.
One or more SELECT statements	Total number of database rows retrieved. NumRowsAffected is zero in each row.
DDL queries such as CREATE, DROP, TRUNCATE	One row with zero NumRowsAffected.

When a query contains multiple statements, Data Integration returns the NumRowsAffected for each statement. NumRowsAffected contains the sum of the rows affected by each INSERT, UPDATE, and DELETE statement in an input row.

For example, a query contains the following statements:

DELETE from Employees WHERE Employee_ID = ‘101’;
SELECT Employee_ID, LastName from Employees WHERE Employee_ID = ‘103’;
INSERT into Employees (Employee_ID, LastName, Address)VALUES (‘102’, 'Gein', '38 Beach Rd')

The DELETE statement affects one row. The SELECT statement does not affect any row. The INSERT statement affects one row.

Data Integration returns one row from the DELETE statement. NumRowsAffected is equal to one. It returns one row from the SELECT statement, NumRowsAffected is zero. It returns one row from the INSERT statement with NumRowsAffected equal to one.

Pass-through fields

Define incoming fields as pass-through fields to pass data through the SQL transformation. The SQL transformation returns data from pass-through fields whether or not the SQL query returns rows.

When the source row contains a SELECT statement, the SQL transformation returns the data in the pass-through field in each row it returns from the database. If the query result contains multiple rows, the SQL transformation repeats the pass through field data in each row.

When a query returns no rows, the SQL transformation returns the pass-through column data and null values in the output fields. For example, queries that contain INSERT, UPDATE, and DELETE statements return no rows. If the query has errors, the SQL transformation returns the pass-through column data, the SQLError message, and null values in the output fields.

To define a pass-through field, click Add in the Pass-Through Fields area, and then select the field you want to pass through the SQL transformation. When you configure an incoming field as a pass-through field, Data Integration adds the field with the suffix "_output" in the Pass-Through Fields area.

If you configure a field as a pass-through field and then change the field name in the source, Data Integration does not update the pass-through field name and no data is passed through the field. In the SQL transformation, delete the old pass-through field and configure the updated incoming field as a pass-through field.

Advanced properties

Advanced properties for stored procedures or functions

Advanced properties for queries