Stored Procedure Transformation to Leverage the Power of Database Scripts

  Level2 Training , Transformations

A Stored Procedure is an important tool for populating and maintaining databases. Since stored procedures allow greater flexibility than SQL statements, database developers and programmers use stored procedures for various tasks within databases. Informatica PowerCenter provides Stored Procedure Transformation to leverage the power of Database Scripting. In this article lets see it in more in detail about how to use Stored Procedure Transformation.

For the demonstration purpose lets consider a scenario.

Customer source data arrives in a flat file from each store. At times, the customer names may contain some invalid data. All customer names should be validated to check for spaces, digits, special characters, etc. so that there is valid customer data in the Data Mart.

Solution

• Use a Stored Procedure transformation to validate the customer name.
1. Connected Stored Procedure Transformation OR
2. Un-Connected Stored Procedure Transformation
• The customer name is passed as a parameter to the Stored Procedure.
• The Stored Procedure returns a ‘V’ value for valid names and ‘I’ for invalid names.

Below is the layout of the completed mapping.

I. Copy the mapping

We will be using the mapping created in the prior demo article. Copy the mapping to continue this exercise.

1. In the Navigator Window, select the mapping M_Custid_x.
2. Select the menu option EDIT | COPY and then select EDIT | PASTE.
3. Rename the mapping as M_CheckCustName_x.

II. Use a Connected Stored Procedure Transformation

1. To create a Stored Procedure transformation
1. Select TRANSFORMATION | CREATE and select Stored Procedure from the drop down, or
2. Click on the  icon from the Transformation toolbar.
3. Enter the name of the transformation as SP_CheckCustName_x.
2. Select the procedure name from the PROCEDURES folder.
3. The Stored Procedure transformation appears with two ports: Name and Flag as shown below. 
4. Double click on the stored procedure transformation. Note : The procedure contains two parameters, Name which is an IN parameter and FLAG, which is an OUT parameter.
5. Delete the existing links between the Source Qualifier and Tgt_Customer_x.
6. Link Firstname port from Source Qualifier into the Name port of the Stored Procedure transformation.
7. Create a Filter transformation and link all ports from Source Qualifier into Filter transformation.
8. Link the FLAG port from Stored Procedure into the Filter.
9. Create the filter condition : FLAG = ‘V’.
10. Link all ports except FLAG into the target.
11. The Sequence Generator transformation will generate the Customer_id in the target. Only rows with valid customer names will pass to the target.
12. The final mapping should look as given below:

III. Load the Target

1. Create a Workflow with the name wf_CheckCustName_Connected_x.
2. Create a session task with the name s_CheckCustName_Cconnected_x
3. Run the Workflow.
4. Monitor the Workflow.

IV. Verify the Results

Select the data from the target table. All the names are clean with no special characters or numbers.

V. Using an Unconnected Stored Procedure Transformation

1. Using the same mapping, remove the existing Stored Procedure transformation.
2. Create the Stored Procedure transformation again. Do not link it to any other transformation. Note : An Unconnected Stored Procedure transformation does not contain any links to other transformations.
3. The ports in the Unconnected Stored Procedure will appear as follows :
4. In the same mapping, create an Expression transformation before the Filter transformation. Link relevant Ports.
5. To call the Stored Procedure from the Expression transformation, enter the expression for the FLAG column, the newly added output port as shown below:
6. FirstName is passed as a parameter to the Stored Procedure and the value returned by the Stored Procedure will be available in the PROC_RESULT variable.
7. Link all ports from Expression transformation into the filter. Complete the rest of the mapping as shown below:

IV. Load the Target

1. Create a Workflow with the name wf_CheckCustName_Unconnected_x.
2. Create a session task with the name s_CheckCustName_Unconnected_x
3. Run the Workflow.
4. Monitor the Workflow.

Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise and subscribe to the mailing list to get the latest tutorials in your mail box.

Share with your friends