Informatica- Stored Procedure Transformation

Here is a topic stored procedure transformation in informatica. Here are two points I want to share here on stored procedures:

Point 1:

What is stored procedure and how this is useful in informatica projects?

Passive Transformation
Connected and Unconnected Transformation
Stored procedures are stored and run within the database.

Join Software Developers Forum SriniForum to discuss more.

The basic usage in realtime is:

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

Key terms used in Stored procedure transformation:

Input/output parameters: IN,OUT parameters to and from stored procedures

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.

Point 2:

I have scenario that I am using multiple Stored procedures in a mapping. Question is how to control execution of multiple Stored Procedures.

The stored procedure accessing type is other than Normal, we need to go to Mapping properties, there, we need to select SP execution order for each access type.