You can use the SQL transformation to call a stored procedure or function in a Microsoft SQL Server, MySQL, ODBC, or Oracle database. The stored procedure or stored function must exist in the database before you create the SQL transformation.
You can call a stored procedure or function with the following types of SQL transformations:
Connected SQL transformation
The transformation is connected to the mapping pipeline. The stored procedure or function runs on a row by row basis and can return a single output parameter or multiple output parameters.
You can map the incoming fields of the SQL transformation to the input fields of a stored procedure. The output fields in the SQL transformation consist of the stored procedure output parameters or return values.
A return value is a code or text string that you define in the stored procedure. For example, a stored procedure can return a value that indicates the date the stored procedure was run. When a stored procedure has a return value, the SQL transformation has a return value field.
Unconnected SQL transformation
The SQL transformation is not connected to the mapping pipeline. An Expression transformation calls the SQL transformation with a stored procedure expression, or the stored procedure runs before or after the mapping.
You can configure the expression to return the stored procedure output to expression output fields and variables. You can call the stored procedure from multiple expressions and nest stored procedures.
You cannot process a stored function with an unconnected SQL transformation.
You might use a stored procedure to perform the following tasks:
•Check the status of a target database before loading data into it.
•Determine if enough space exists in a database.
•Perform a specialized calculation.
•Retrieve data by a value.
•Drop and re-create indexes.
•Remove temporary tables.
•Verify that a table exists in a database
You can use a stored procedure to perform a calculation that you would otherwise make part of a mapping. For example, if you have a stored procedure to calculate sales tax, perform that calculation in an SQL transformation instead of re-creating the calculation in an Expression transformation.
When you run a mapping, the SQL transformation passes input parameters to the stored procedure. The stored procedure passes the return value or values to the output fields of the transformation.
Connected SQL transformation example
Your mapping includes user IDs in the data flow. You want to include user names in addition to user IDs.
You have a stored procedure that matches user IDs with user names in the database. You add an SQL transformation to your mapping, select the stored procedure, and map the userId incoming field with the userId input field in the stored procedure. You check the Output Fields tab for the SQL transformation to confirm that it includes the username field. When you run the mapping, the username value is returned with the user ID.
Unconnected SQL transformation example
Your mapping includes employee salary data and you want to update each employee's salary with a raise.
You have a stored procedure that calculates employee salary increases. The stored procedure returns the new salary and the percentage of increase. You add an unconnected SQL transformation and select the stored procedure.
You then add an Expression transformation to the mapping pipeline. In the Expression transformation, you add a variable field to capture the new salary. You add an output field and use the stored procedure function to configure the expression. You configure the arguments so that the output field returns the increase percentage and you create a second output field to return the new salary. You then map the new output fields to the downstream transformation.