Stored Procedure Transformation In Informatica 4i6ex

Stored Procedure Transformation

Stored Procedure Transformation in Informatica

Author: Chandrashekar.S.N E-mail: [email protected]

1

Stored Procedure Transformation

Contents: 1. Overview...............................................................................................3 1.1.Stored Procedure Transformation connection mode......................................................................3 1.2.Using a Stored Procedure in a Mapping........................................................................................3

2. Writing a Stored Procedure................................................................4 2.1. Sample Stored Procedure............................................................................................................. 4 2.2. ed databases.................................................................................................................... 4

3. Creating a Stored Procedure Transformation...................................4 3.1. Changing the Stored Procedure................................................................................................... 5

4. Configuring a Stored Procedure Transformation.............................5 4.1. Configuring a Connected Transformation.....................................................................................5 4.2. Configuring a Un-Connected Transformation................................................................................6

5. Creating a sample mapping which uses stored procedure Transformation (connected)...................................................................7 6. Summary............................................................................................10 7. Appendix.............................................................................................10 7.1 Comparison of Connected and Unconnected Stored Procedure Transformations.......................10 7.2 Stored Procedure Transformation Implementation Tips...............................................................11

8.References:.........................................................................................11

2

Stored Procedure Transformation

1. Overview Stored Procedure transformation (ive type) is used for populating and maintaining databases. A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-s may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time). 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 -defined variables, conditional statements, and other powerful programming features.

Note: The stored procedure must exist in the source, target, or any database with a valid connection to the Integration Service before creating a Stored Procedure transformation

1.1.

Stored Procedure Transformation connection mode

Stored procedures run in either connected or unconnected mode. The mode you use depends on what the stored procedure does and how you plan to use it in a session 

Connected. The flow of data through a mapping in connected mode also es through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure.



Unconnected. The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.

Note: Stored procedure should contain at least one input and one output parameter Using a Stored Procedure in a Mapping Steps to be followed: 

Create the stored procedure in the database and test it.



Import or create the Stored Procedure transformation.



Determine whether to use the transformation as connected or unconnected.



If connected, map the appropriate input and output ports.



If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.



Since stored procedures can run before or after the session, you may need to specify when the unconnected transformation should run. On the other hand, if the stored procedure is called

3

Stored Procedure Transformation from another transformation, you write the expression in another transformation that calls the stored procedure. 

Configure the session.

2. Writing a Stored Procedure You write SQL statements to create a stored procedure in the database. You can also add other Transact-SQL statements and database-specific functions to a stored procedure.

2.1. Sample Stored Procedure Below procedure illustrates a stored procedure in Microsoft SQL Server 2005

Create procedure sp_test1 (@cd_val_id int,@cd_tbl_id int output,@cd_tbl_desc varchar(192) output) as select @cd_tbl_id=cd_tbl_id,@cd_tbl_desc=expl_txt from cd_val where cd_val_id=@cd_val_id return 0 Stored procedure takes cd_val_id as input and returns cd_tbl_id and cd_tbl_desc as the output values.Input parameter cd_val_id is ed from informatica and return values is captured in output parameters cd_tbl_id and cd_tbl_desc.

2.2. ed databases The stored procedure transformation is ed by Oracle, and other databases, such as Informix, Microsoft SQL Server, and Sybase. Parameter Types: There are three possible parameter types in stored procedures: 

IN. Defines the parameter something that must be ed to the stored procedure.



OUT. Defines the parameter as a returned value from the stored procedure.



INOUT. Defines the parameter as both input and output. Only Oracle s this parameter type.

4

Stored Procedure Transformation

3. Creating a Stored Procedure Transformation After you configure and test a stored procedure in the database, you must create the Stored Procedure transformation in the Mapping Designer. There are two ways to configure the Stored Procedure transformation: 

Use the Import Stored Procedure dialog box to configure the ports used by the stored procedure.



Configure the transformation manually, creating the appropriate ports for any input or output parameters.

Stored Procedure transformations are created as Normal type by default, which means that they run during the mapping, not before or after the session.

3.1. Changing the Stored Procedure If the number of parameters or the return value in a stored procedure changes, you can either re-import it or edit the Stored Procedure transformation manually. The Designer does not the Stored Procedure transformation each time you open the mapping. After you import or create the transformation, the Designer does not validate the stored procedure. The session fails if the stored procedure does not match the transformation. Note: For more information on creating a stored procedure transformation please go through informatica help.

5

Stored Procedure Transformation

4. Configuring a Stored Procedure Transformation 4.1. Configuring a Connected Transformation Below figure shows a mapping that sends the cd_val_id from the Source Qualifier to an input parameter in the Stored Procedure transformation and retrieves an output parameters (cd_tbl_id,cd_tbl_desc) from the Stored Procedure transformation that is sent to the target. Every row of data in the Source Qualifier transformation es data through the Stored Procedure transformation:

Required input parameters are specified as the input ports of the Stored Procedure transformation. Output parameters appear as output ports in the transformation. A return value is also an output port, and has the R value selected in the transformation Ports configuration. For a normal connected Stored Procedure to appear in the functions list, it requires at least one input and one output port. Output parameters and return values from the stored procedure are used as any other output port in a transformation. You can map the value of these ports directly to another transformation or target.

4.2. Configuring a Un-Connected Transformation An unconnected Stored Procedure transformation is not directly connected to the flow of data through the mapping. Instead, the stored procedure runs either: 

From an expression. Called from an expression written in the Expression Editor within another transformation in the mapping.



Pre- or post-session. Runs before or after a session.

The sections below explain how you can run an unconnected Stored Procedure transformation. Calling a Stored Procedure from an Expression In an unconnected mapping, the Stored Procedure transformation does not connect to the pipeline. Below figure shows a mapping with an Expression transformation that references the Stored Procedure transformation:

6

Stored Procedure Transformation

However, just like a connected mapping, you can apply the stored procedure to the flow of data through the mapping. In fact, you have greater flexibility since you use an expression to call the stored procedure, which means you can select the data that you to the stored procedure as an input parameter. Note: For more information on configuring stored procedure transformation please go through informatica help.

7

Stored Procedure Transformation

5. Creating a sample mapping which uses stored procedure Transformation (connected) 

Create the stored procedure in the database:

Below procedure was written in the database (Microsoft SQL Server 2005) create procedure sp_test1 (@cd_val_id int,@cd_tbl_id int output,@cd_tbl_desc varchar(192) output) as select @cd_tbl_id=cd_tbl_id,@cd_tbl_desc=expl_txt from cd_val where cd_val_id=@cd_val_id return 0 stored procedure takes cd_val_id as input and returns cd_tbl_id and cd_tbl_desc as the output values. Input parameter cd_val_id is ed from informatica and return values is captured in output parameters cd_tbl_id and cd_tbl_desc.



Create a mapping in designer.



Import the stored procedure

8

Stored Procedure Transformation



Sample mapping will look as below.



Configuring the stored procedure transformation

9

Stored Procedure Transformation

Above window shows the properties that can be set for a stored procedure transformation. Stored procedure name: It is the name used by integration service to call stored procedure. Connection information: Here you can give the connection string to connect to database. By default it is set to $target variable .You can overwrite this in workflow manager. Call text: Text used by integration service to called stored procedure when it is not of normal type. Execution order: Order in which the stored procedure need to be called in case of multiple stored procedures. Used in case stored procedure is a not of normal type. Tracing level: It is used for debugging purpose. 

Setting Stored procedure properties in workflow manager.

10

Stored Procedure Transformation

11

Stored Procedure Transformation

6. Summary In Summary, we can say that stored procedure transformation can be used for populating and managing the database. It can be used similar to lookup transformation. Stored Procedure Transformation can be used in connected or unconnected mode. It can be used to do calculations which could otherwise make mapping complex.

7. Appendix 7.1 Comparison of Connected and Unconnected Stored Procedure Transformations

Unconnected stored procedure transformation is used when:  Stored procedure need to be called before or after a session  Stored procedure need to be run once either as pre- or post-session  Stored procedure need to be run based on the row ing through stored procedure  Stored procedure need to be called multiple times in a mapping Connected stored procedure transformation is used when:  Stored procedure need to be run for every row ing through the stored procedure transformation  Stored procedure returns a single or multiple output parameters by accepting a input parameters Note: unconnected stored procedure transformation can be used where ever connected stored procedure transformation is used but not vise versa.

7.2 Stored Procedure Transformation Implementation Tips 

 

One cannot use a stored procedure without an input and an output port. You need to have at least one input and one output parameter. If there is a stored procedure without an input and output parameter then u can use it with by creating one dummy input and one dummy output parameter. Results sets from an output of a stored procedure cannot be handled in informatica. It can be handled by using the query contained in a stored procedure in the Source Qualifier (if it is a simple query). The maximum length of an SQL query that can be used in Source Qualifier is approximately 32000 characters.

8.References:  

Informatica Documentation Informatica developer network

12

Stored Procedure Transformation ---------------------------****************************************------------------------

13