Informatica SQL Transformation

SQL Transformation

You can insert, delete, update, and retrieve rows from a database using SQL transformation midstream in a (Mapping)pipeline.

The SQL transformation runs in two mods
1) Script mode
2) Query mode

Lets see how to use script mode in detail.

Script mode can be used for Delete , Insert and Update data in the table. Create Table script.

Lets see the steps:-
Create a file with the scripts like below and name the file (ex:- Create_script).

Create a another file which will have the path and name of the above script and this file will act as our source file in the mapping.

Steps of the mapping:-

Create source Flat file with the a column (ex:- FILE_NAME)
Create Target Flat File with 2 columns for Result and Error
Create SQL Transformation for Script mode.
And use the SCRIPT_NAME.txt as source file(don't use the actual script as your source).

Follow the above screen shot to select SQL transformation.

And then select the script mode radio button as shown above. Also select the data base where you want to create the table (in our example we are creating 2 tables in Oracle DB).

** when you select Script mode by default (SQL transformation will run in passive check box  will be checked and disabled).
The final mapping will look like below.

Run the session and then check if the tables PERSONS AND ORDERS are created in Oracle DB.
before that lets see if those tables are present in our DB.

Those 2 tables are not present on the DB. Lets run our workflow and see if the SQL transformation has created those 2 tables.                                                                                                                         

Result                                                                                                                                     

Lets check in the DB                                                                                                                                 

 

As we can see 2 tables created Orders and Persons.