Let’s start checking out what the official documentation has to say:
“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. The Stored Procedure transformation is a passive transformation. You can configure a connected or unconnected Stored Procedure transformation.
A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script. Stored procedures are stored and run within the database. You can run a stored procedure with the EXECUTE SQL statement in a database client tool, just as you can run SQL statements. Unlike standard SQL, however, stored procedures allow user-defined variables, conditional statements, and other powerful programming features.”
- Stored procedures allow user-defined variables, conditional statements, and other powerful programming features
- If a stored procedure returns a result set rather than a single return value, the Stored Procedure transformation takes only the first value returned from the procedure.
Connected and Unconnected
Stored procedures can be either connected or unconnected.
Connected: use this if you want your Stored Procedure transformation to receive data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.
Unconnected: here the Stored Procedure transformation either runs before or after the session, or is called by an expression in another transformation in the mapping.
The following table compares connected and unconnected transformations:
Stored Procedure runs
- Unconnected: If no transformation references the Stored Procedure transformation, you have the option to run the stored procedure once before or after the session.
- Unconnected: If a transformation do references the Stored Procedure transformation, then it will be runed every time a row passes trough that transformation.
The following list describes the options for running a Stored Procedure transformation:
- Normal. The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. This is useful for calling the stored procedure for each row of data that passes through the mapping, such as running a calculation against an input port. 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 verifying target tables or disk space on the target system.
- 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.
Some more points:
- You can run more than one Stored Procedure transformation in different modes (connected or unconnected) in the same mapping.
- You cannot run the same instance of a Stored Procedure transformation in both connected and unconnected mode in a mapping.
- The Integration Service opens the database connection when it encounters the first stored procedure. The database connection remains open until the Integration Service finishes processing all stored procedures for that connection. The Integration Service closes the database connections and opens a new one when it encounters a stored procedure using a different database connection.
- To run multiple stored procedures that use the same database connection, set these stored procedures to run consecutively.
Stored Procedured Transformation Hands On
Enough talking, let’s do some practice. First the Stored Procedure itself, here’s the one we created:
USE [PWC]
GO
/****** Object: StoredProcedure [dbo].[SP_GET_ITEM_COUNT] Script Date: 7/28/2022 7:47:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GET_ITEM_COUNT] @ITEM_ID INT, @outRes as INT OUTPUT
AS SELECT @outRes = COUNT(*) FROM ORDER_ITEMS WHERE ITEM_ID = @ITEM_ID
return @outRes
GO
This Stored Procedure is doing what? It is selecting count(*) from ORDER_ITEMS, filtering this select by ITEM_ID and then storing it’s result inside @outRes.
Now, to the mapping.
We have a pretty straightforward mapping. By now you should be already used to all transformations and objects in the image above.
Key point here is our Stored Procedure transformation.
Port-wise no surprises. We just unchecked Input port for outRes as we won’t make any use of that port.
For properties we are using type Normal, as this is the only that we can use for a connected Stored Procedure.
Conclusion
Our hands on example is very timid, and I believe that the real power of Stored Procedure transformations are in the unconnected types for Pre and Post target/post loads. However, for our certification point of view it’s vital that we understand how SP transformation behaves, and I hope this post can help us with that!
Pedro Carvalho 
One thought on “Informatica Powercenter – Stored Procedure Transformation”