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.
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.
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.