PowerCenter - Stored Procedure Transformation


Stored procedure in the context of PowerCenter.

Create a stored procedure

  • Create a Stored Procedure transformation and name it SP_TransformationName.

The Import Stored Procedure dialog box appears.

  • Select the ODBC connection for the source database. Enter a user name, owner name, and password. Click Connect.
  • Select the stored procedure from the list and click OK.
  • In the Create Transformation dialog box, click Done.

The Stored Procedure transformation appears in the mapping.

  • Open the Stored Procedure transformation, and click the Properties tab.
  • Click the Open button in the Connection Information section.

The Select Database dialog box appears.

  • Select the source database and click OK.

You can call stored procedures in both source and target databases.

You can also select the built-in database connection variable, $Source. When you use $Source or $Target, the PowerCenter - Integration Service determines which source database connection to use when it runs the PowerCenter - Sessions. If it cannot determine which connection to use, it fails the session. For more information about using $Source and $Target in a Stored Procedure transformation, see “Stored Procedure Transformation”in the Transformation Guide.

  • Click OK
  • Connect the ports in the mapping

Documentation / Reference

  • PC_861_TransformationGuide.pdf (Stored Procedure page 389 chapter 27)

Powered by ComboStrap