Stored Procedure Templates

Version 4

    This Integration Template allows the capability to call DB Stored Procedure from Informatica Cloud by using the Custom Integration Task (CIT). Import the Integration Template .xml file and configure template parameters.

     

    Understanding the Template

     

    p1.png

     

    The template uses filter transform for filtering out data sets. The SP transform executes the stored procedure call to the DB connection defined when setting the CIT parameters.

     

    Setting up the display properties during the import process of the Template

    p4.png

     

     

    Creating custom integration task based on the templates

    1. Choose the Integration Template

    download.png

     

         2. Choose the source

         3. Choose the target

         4. Complete the filter, connection information, and other parameters

     

    p3.png

    3.png

    4.png

    Above is an example of the field coming in from the source and the input field for the Stored Procedure, in this example they both are Item_ID but they can be different and not necessarily the same.


    5.png


    Above is an example of the field coming out from the Stored Procedure call, and getting mapped to the Target system fields.


     

    Task Parameters

     

    NameTypeValue
    $Source_table1$Source
    Source Connection:Elvis enterprise ff
    Selected object:ITEMS.csv
    $target_table1$Target
    Target Connection:Elvis enterprise ff
    Selected object:SP_Temp_out.out
    $2_SP_connection$Connection
    Connection:Lab SQLServer_Enterprise
    $1_Filter_Condition$Other
    IIF(ISNULL(ITEM_ID),FALSE,TRUE)
    $3_Source_Fields$Other
    ITEM_ID
    $4_Stored_Proc_Inputs$Other
    ITEM_ID
    $5_SP_Expression_Text$Other
    SP_GET_Order_ITEM_COUNT (integer IN ITEM_ID, integer OUT Items_Ordered)
    $6_Stored_Proc_Outputs$Other
    Items_Ordered
    $7_Target_Fields$Other
    Number_Ordered

     

     

    Sample Stored Procedure Used in this example

     

    download (4).pngp2.png

    Above is an example of calling a Stored Procedure with two input and two output fields.