Stored Procedure transformation

1. Stored Procedure transformation is a passive transformation. Stored Procedure transformation is a Connected and Unconnected Transformation
2. Stored procedures are stored and run within the database.
3. A Stored Procedure transformation is an important tool for populating and maintaining databases. Database administrators create stored procedures to automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

1. Check the status of a target database before loading data into it.
2. Determine if enough space exists in a database.
3. Perform a specialized calculation.
4. Drop and recreate indexes. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure

• One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:
• Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.
• Return values: Value returned by Stored Procedure if any.
• Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop.

Specifying when the Stored Procedure Runs

• Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.
• Pre-load of the Source: 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.
• Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.
• Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.
• Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Using a Stored Procedure in a Mapping

1. Create the stored procedure in the database.
2. Import or create the Stored Procedure transformation.
3. Determine whether to use the transformation as connected or unconnected.
4. If connected, map the appropriate input and output ports.
5. If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
6. Configure the session.