Source Pre-load: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.
Source Post-load: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
Target Pre-load: Before the session sends data to the target, the stored procedure runs. This is useful for verifying target tables or disk space on the target system.
Target Post-load: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.
To call a stored procedure, do the following:
Create the Stored Procedure transformation in the mapping.
Enter the name of the Stored Procedure. If you have imported it, this should be set correctly. If you manually create the Stored Procedure, then enter name.
Select the database that contains the stored procedure in Connection Information.
Enter the Call Text of the Stored Procedure.
The Call text is the name of the Stored Procedure, followed by any applicable input parameters in parentheses. If there are no input parameters, you must include an empty pair of parentheses, else the call to the stored procedure fails. You do not have to include the SQL statement EXEC or use the :SP keyword.
To call a Stored Procedure drop_index, enter the following:
To pass a string input parameter, enter it without quotes. If the string has spaces in it, enclose the parameter in double quotes.
To call a stored procedure delete_column() requiring a table name as an input parameter, enter the following:
HOW TO: Pass parameters containing single quotes to stored procedure in a PowerCenter mapping (KB 105541)
HOW TO: Change the execution order of unconnected Stored Procedure transformations in a PowerCenter mapping (KB 10440)
HOW TO: Capture a Stored Procedure error when running a PowerCenter session (KB 20457)
HOW TO: Pass a mapping variable to a Stored Procedure transformation (KB 18430)
What can we do to improve this information (2000 or fewer characters)