SQL Transformation is an Active/Passive & Connected transformation. Through SQL Transformation we can insert, delete, update, and retrieve rows from a database at run time. The transformation processes external SQL scripts or SQL queries created in an SQL editor and returns rows and database errors. Through the SQL transformation we can create the tables in a workflow.

  • The following SQL statements can be used with SQL transformation.
  • Data Definition SQL Statements (ALTER, COMMENT, CREAT, DROP, RENAME, TRUNCATE)
  • Data Manipulation SQL Statements (CALL, DELETE, EXPLAIN PLAN, INSERT, LOCK TABLE, MERGE, SELECT, UPDATE)
  • Data Control Language SQL Statements (GRANT, REVOKE)
  • Transaction Control SQL Statements (COMMIT, ROLLBACK)

The SQL transformation runs in one of the following modes:

  • Script mode
  • Query mode

Script mode: Select a passive transformation that executes external SQL scripts.

Query mode: Select an active transformation that executes dynamic SQL queries.

Script Mode:

The SQL transformation runs ANSI SQL scripts that are externally located. We have to pass a script name to the transformation with each input row.

  • The script file name contains the complete path to the script file. (e.g.- C:\Documents and Settings\Desktop\ Test.txt)
  • The SQL transformation outputs one row for each input row.

  • In Script mode SQL transformation works as Passive Transformation.
  • Use script mode to run data definition queries such as creating or dropping tables
  • Static or dynamic database connection can be use with script mode.
  • The script file must be accessible by the Integration Service. The Integration Service must have read permissions on the directory that contains the script.

Scripting languages such as Oracle PL/SQL or Microsoft/Sybase T-SQL cannot be use in the script.

  • The Integration Service ignores the output of any SELECT statement you include in the SQL script. The SQL transformation in script mode does not output more than one row of data for each input row.

Query Mode:

When the SQL query contains a SELECT statement and the transformation has a pass-through port, the transformation returns data to the pass-through port whether or not the query returns database data. The SQL transformation returns a row with NULL data in the output ports.

When the SQL query contains an INSERT, UPDATE, or DELETE clause, the transformation returns data to the SQLError port, the pass-through ports, and the NumRowsAffected port when it is enabled. If we add more output ports in the transformation, they are assigned NULL data values.

Joiner transformation Vs SQL Transformation:

When we have sources from same Database, the general practice is that we use SQL Transformation.

  • A SQL Transformation fires the Query on the database which we have specified, Where as the Joiner transformation gets the data from both the databases and then stores it in Cache and then applies ‘where’ condition. It will consume the memory.
  • When we have sources from different DB’s then, we use Joiner transformation.
Posted by Muralidhar Reddy
Comments (6)
July 13th, 2011

Comments (6)

Murali - July 23rd, 2011

Hi Laxmi, When you configure a session for a respective mapping, you can instruct the informatica Integration Service to either treat all rows in the same way (example, treat all rows as inserts), or use instructions defined into the session mapping to flag rows for different database operations. In PowerCenter, you set the update strategy at two different levels: 1) In the mapping 2) At the session level In the mapping: Within a mapping, you use the Update Strategy trasformation to flag rows for insert, delete, update, or reject. At session level we have the options as metioned below.. Insert --> Flags as the rows inserts to the target. Update as update--> Flags all the rows as update to the target irrespective of the condition you specified in the update strategy transformation. Update as Insert --> Treats updates as inserts. Update else insert --> checks for the row in the target and inserts if there is no row exists. If exits it update the particular row in the target. In informatica the mapping level settings will be overriden by session level properties.If you define the same or different properties in the mapping as well as at the session level. The integration service will consider the session level properties we define.

Laxmi - July 21st, 2011

Hi Murali, Can u please explain me in which case we choose update as update,insert else update and delete options..Please explain me with scenario..What happens if i put both update strategy t/r in mapping & enable the above chk boxes in session level?how internally it will work? What is the difference b/n truncate and delete here? if i select both truncate target table and delete options in session with bulk load how it will work?

Laxmi - July 21st, 2011

Thank you Murali..

Srinivasulu - July 20th, 2011

we can use the SQl transformation , but while inserting the records , it will take time to loadi it.

Murali - July 14th, 2011

Hi Laxmi, If you want to perform an insert and update by using SQL transformation definitely we should use the SP, cursors etc..which SQL transformation wont support.The alternative what i can suggest you is you can use router and put two grouos for insert and update then use the two target update/ insert overides. And dont forget to select the appropriate properties(insert/Update) at the session level.

Laxmi - July 14th, 2011

Hi Murali, Thanks for your posting.I have a small doubt,can we use SQL t/r instead of update strategy t/r in scd's.when I was interviewd by a person he was asking me the question like " Without using update strategy t/r how can u perform updations & insertions in scd2?" Please explain me all possible ways to do this scenario...

Comments are closed.