Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Aggregator transformation
  6. Cleanse transformation
  7. Data Masking transformation
  8. Deduplicate transformation
  9. Expression transformation
  10. Filter transformation
  11. Hierarchy Builder transformation
  12. Hierarchy Parser transformation
  13. Hierarchy Processor transformation
  14. Input transformation
  15. Java transformation
  16. Java transformation API reference
  17. Joiner transformation
  18. Labeler transformation
  19. Lookup transformation
  20. Mapplet transformation
  21. Normalizer transformation
  22. Output transformation
  23. Parse transformation
  24. Python transformation
  25. Rank transformation
  26. Router transformation
  27. Rule Specification transformation
  28. Sequence Generator transformation
  29. Sorter transformation
  30. SQL transformation
  31. Structure Parser transformation
  32. Transaction Control transformation
  33. Union transformation
  34. Velocity transformation
  35. Verifier transformation
  36. Web Services transformation

Transformations

Transformations

Stored procedure or function processing

Stored procedure or function processing

You can use the SQL transformation to call a stored procedure or function in a Microsoft SQL Server, MySQL, ODBC, or Oracle database. The stored procedure or stored function must exist in the database before you create the SQL transformation.
You can call a stored procedure or function with the following types of SQL transformations:
Connected SQL transformation
The transformation is connected to the mapping pipeline. The stored procedure or function runs on a row by row basis and can return a single output parameter or multiple output parameters.
You can map the incoming fields of the SQL transformation to the input fields of a stored procedure. The output fields in the SQL transformation consist of the stored procedure output parameters or return values.
A return value is a code or text string that you define in the stored procedure. For example, a stored procedure can return a value that indicates the date the stored procedure was run. When a stored procedure has a return value, the SQL transformation has a return value field.
Unconnected SQL transformation
The SQL transformation is not connected to the mapping pipeline. An Expression transformation calls the SQL transformation with a stored procedure expression, or the stored procedure runs before or after the mapping.
You can configure the expression to return the stored procedure output to expression output fields and variables. You can call the stored procedure from multiple expressions and nest stored procedures.
You cannot process a stored function with an unconnected SQL transformation.
You might use a stored procedure to perform the following tasks:
  • Check the status of a target database before loading data into it.
  • Determine if enough space exists in a database.
  • Perform a specialized calculation.
  • Retrieve data by a value.
  • Drop and re-create indexes.
  • Remove temporary tables.
  • Verify that a table exists in a database
You can use a stored procedure to perform a calculation that you would otherwise make part of a mapping. For example, if you have a stored procedure to calculate sales tax, perform that calculation in an SQL transformation instead of re-creating the calculation in an Expression transformation.
When you run a mapping, the SQL transformation passes input parameters to the stored procedure. The stored procedure passes the return value or values to the output fields of the transformation.

Connected SQL transformation example

Your mapping includes user IDs in the data flow. You want to include user names in addition to user IDs.
You have a stored procedure that matches user IDs with user names in the database. You add an SQL transformation to your mapping, select the stored procedure, and map the userId incoming field with the userId input field in the stored procedure. You check the
Output Fields
tab for the SQL transformation to confirm that it includes the username field. When you run the mapping, the username value is returned with the user ID.

Unconnected SQL transformation example

Your mapping includes employee salary data and you want to update each employee's salary with a raise.
You have a stored procedure that calculates employee salary increases. The stored procedure returns the new salary and the percentage of increase. You add an unconnected SQL transformation and select the stored procedure.
You then add an Expression transformation to the mapping pipeline. In the Expression transformation, you add a variable field to capture the new salary. You add an output field and use the stored procedure function to configure the expression. You configure the arguments so that the output field returns the increase percentage and you create a second output field to return the new salary. You then map the new output fields to the downstream transformation.