Table of Contents

About

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.

Powercenter Transformation Store Procedure Import

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

Powercenter Transformation Store Procedure Properties

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

Powercenter Transformation Store Procedure

Documentation / Reference

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