Thursday, March 14, 2013

SQL Transformation in Script Mode Examples - Informatica

This is continuation to my previous post on SQL Transformation in Query Mode. Here we will see how to use SQL transformation in script mode.

Script Mode

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
  • ScriptName (Input port) : Receives the name of the script to execute for the current row.
  • ScriptResult (output port) : Returns PASSED if the script execution succeeds for the row. Otherwise FAILED.
  • ScriptError (Output port) : Returns errors that occur when a script fails for a row.

Rules and Guidelines for script mode

You have to follow the below rules and guidelines when using the sql transformation in script mode:

  • You can run only static sql queries and cannot run dynamic sql queries in script mode.
  • You can include multiple sql queries in a script. You need to separate each query with a semicolon.
  • The integration service ignores the output of select statements in the SQL scripts.
  • You cannot use procedural languages such as oracle plsql or Microsoft/Sybase T-SQL in the script.
  • You cannot call a script from another script. Avoid using nested scripts.
  • The script must be accessible to the integration service.
  • You cannot pass arguments to the script.
  • You can use mapping variables or parameters in the script file name.
  • You can use static or dynamic database connection in the 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

Now we will create a mapping to execute the script files using the SQL transformation. Follow the below steps to create the mapping.
  • Go to the mapping designer tool, source analyzer and create the source file definition with the structure as the $PMSourceFileDir/Script_names.txt file. The flat file structure is shown in the below image.
  • Go to the warehouse designer or target designer and create a target flat file with result and error ports. This is shown in the below image.
  • Go to the mapping designer and create a new mapping.
  • Drag the flat file into the mapping designer.
  • Go to the Transformation in the toolbar, Create, select the SQL transformation, enter a name and click on create.
  • Now select the SQL transformation options as script mode and DB type as Oracle and click ok.
  • The SQL transformation is created with the default ports.
  • Now connect the source qualifier transformation ports to the SQL transformation input port.
  • Drag the target flat file into the mapping and connect the SQL transformation output ports to the target.
  • Save the mappping. The mapping flow image is shown in the below picture.
  • Go to the workflow manager, create a new mapping and session.
  • Edit the session. For source, enter the source file directory, source file name options as $PMSourceFileDir\ and Script_names.txt respectively. For the SQL transformation, enter the oracle database relational connection.
  • Save the workflow and run it.

No comments:

Post a Comment