In a script mode, you have to create the sql scripts in a text file. The SQL transformation runs your sql scripts from these text files. You have to pass each script file name from the source to the SQL transformation ScriptName port. The script file name should contain a complete path to the script file. The SQL transformation acts as passive transformation in script mode and returns one row for each input row. The output row contains results of the query and any database error.
SQL Transformation default ports in script mode
In script mode, By default three ports will be created in SQL transformation. They are
Rules and Guidelines for script mode
You have to follow the below rules and guidelines when using the sql transformation in script mode:
Note: Use SQL transformation in script mode to run DDL (data definition language) statements like creating or dropping the tables.
Create SQL Transformation in Script Mode
We will see how to create sql transformation in script mode with an example. We will create the following sales table in oracle database and insert records into the table using the SQL transformation.
Script Name: $PMSourceFileDir/sales_ddl.txt Create Table Sales ( Sale_id Number, Product_name varchar2(30), Price Number ); Script Name: $PMSourceFileDir/sales_dml.txt Insert into sales values(1,'Samsung',2000); Insert into sales values(2,'LG',1000); Insert into sales values(3,'Nokia',5000);
I created two script files in the $PMSourceFileDir directory. The sales_ddl.txt contains the sales table creation statement and the sales_dml.txt contains the insert statements. These are the script files to be executed by SQL transformation.
We need a source which contains the above script file names. So, I created another file in the $PMSourceFileDir directory to store these script file names.
File name: $PMSourceFileDir/Script_names.txt > cat $PMSourceFileDir/Script_names.txt $PMSourceFileDir/sales_ddl.txt $PMSourceFileDir/sales_dml.txt
Now we will create a mapping to execute the script files using the SQL transformation. Follow the below steps to create the mapping.