SQL Transformations in Informatica 9.5
SQL Transformations takes the input in the variety of SQL queries or SQL scripts that you create in the SQL editor and process the query and returns rows and database errors. It’s basically an Active transformation that you can organize as passive also. For Eg: We may have a scenario where we need to create database tables in a workflow. Here we can make use of SQL Transformation and create these tables before we move into the next flow.
Configurations in SQL Transformation
There are two modes
- Script Mode: We pass the name of script as an Input to the transformation in this mode.
- Query Mode: We pass a Query having variables or parameters in this mode.
Database Types: The type of database that we are connecting and the type of database connection that we use like FTP or any other.
Script mode: As mentioned above we pass the script name as input to the transformation. And it returns the output rows are errors.
Input port: It has the scripts name from previous level.
Output Port: Proceeds PASSED if the script execution succeeds for the row otherwise contains FAILED.
Query Mode: When a SQL transformation runs in query mode, it executes a SQL query that you define in the transformation. This creates an active transformation and it returns multiple rows and the SQL Editor validates the query.
You can create the following types of SQL queries in the SQL transformation
Static SQL Query: The Query statement does not change, but you can use query parameters to change the data. The Integration Service prepares the query once and runs the query for all input rows.
Dynamic SQL Query: You can change the Query statements and the data. The Integration Service prepares a query for each input row.
SQL Transformation Properties
After you create the SQL transformation, you can define the ports and set attributes in the following Transformation tabs:
Ports: Displays the transformation ports and attributes that you create on the SQL Ports tab.
Properties: SQL transformation general properties.
