Table of Contents

Search

  1. Preface
  2. Working with Transformations
  3. Aggregator Transformation
  4. Custom Transformation
  5. Custom Transformation Functions
  6. Data Masking Transformation
  7. Data Masking Examples
  8. Expression Transformation
  9. External Procedure Transformation
  10. Filter Transformation
  11. HTTP Transformation
  12. Identity Resolution Transformation
  13. Java Transformation
  14. Java Transformation API Reference
  15. Java Expressions
  16. Java Transformation Example
  17. Joiner Transformation
  18. Lookup Transformation
  19. Lookup Caches
  20. Dynamic Lookup Cache
  21. Normalizer Transformation
  22. Rank Transformation
  23. Router Transformation
  24. Sequence Generator Transformation
  25. Sorter Transformation
  26. Source Qualifier Transformation
  27. SQL Transformation
  28. Using the SQL Transformation in a Mapping
  29. Stored Procedure Transformation
  30. Transaction Control Transformation
  31. Union Transformation
  32. Unstructured Data Transformation
  33. Update Strategy Transformation
  34. XML Transformations

Transformation Guide

Transformation Guide

Manually Creating Stored Procedure Transformations

Manually Creating Stored Procedure Transformations

To create a Stored Procedure transformation manually, you need to know the input parameters, output parameters, and return values of the stored procedure, if there are any. You must also know the datatypes of those parameters, and the name of the stored procedure. All these are configured through Import Stored Procedure.
To create a Stored Procedure transformation:
  1. In the Mapping Designer, click Transformation > Create, and then select Stored Procedure.
    The naming convention for a Stored Procedure transformation is the name of the stored procedure, which happens automatically. If you change the transformation name, then you need to configure the name of the stored procedure in the Transformation Properties. If you have multiple instances of the same stored procedure in a mapping, you must perform this step.
  2. Click Skip.
    The Stored Procedure transformation appears in the Mapping Designer.
  3. Open the transformation, and click the Ports tab.
    You must create ports based on the input parameters, output parameters, and return values in the stored procedure. Create a port in the Stored Procedure transformation for each of the following stored procedure parameters:
    • An integer input parameter
    • A string output parameter
    • A return value
    For the integer input parameter, you would create an integer input port. The parameter and the port must be the same datatype and precision. Repeat this for the output parameter and the return value.
    The R column should be selected and the output port for the return value. For stored procedures with multiple parameters, you must list the ports in the same order that they appear in the stored procedure.
  4. Click the Properties tab.
    Enter the name of the stored procedure in the Stored Procedure Name row, and select the database where the stored procedure exists from the Connection Information row.
  5. Click OK.
Although the repository validates and saves the mapping, the Designer does not validate the manually entered Stored Procedure transformation. No checks are completed to verify that the proper parameters or return value exist in the stored procedure. If the Stored Procedure transformation is not configured properly, the session fails.

0 COMMENTS

We’d like to hear from you!