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.                                                                                                                                                 
Lets check in the DB                                                                                                                                    
As we can see 2 tables created Orders and Persons.                                                                                    


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s