Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(2 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Call a Stored Procedure automatically when running a PowerCenter session
Solution
An Unconnected Stored Procedure can be called automatically in the following situations:
  • 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:

  1. Create the Stored Procedure transformation in the mapping.

  2. 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.

  3. Select the database that contains the stored procedure in Connection Information.

  4. 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.

    Example

    To call a Stored Procedure drop_index, enter the following:

    drop_index()

    To pass a string input parameter, enter it without quotes. If the string has spaces in it, enclose the parameter in double quotes.

    Example

    To call a stored procedure delete_column() requiring a table name as an input parameter, enter the following:

    delete_column(test)

  5. Select the execution order as need from the List (Source/Target Pre/Post Load).
More Information
A sample Mapping m_target_pre_sql_test.XML is attached for reference.
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Architect; Developer
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:1/21/2016 12:42 AMID:21504
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)