SQL Transformation in Script Mode Examples – Informatica

  • Updated
  • Posted in Programming
  • 5 mins read


This is continuation to my earlier submit on SQL Transformation in Query Mode. Here we’ll see the best way to use SQL transformation in script mode.

Script Mode

In a script mode, it’s important to create the sql scripts in a textual content file. The SQL transformation runs your sql scripts from these textual content recordsdata. You must cross every script file identify from the supply to the SQL transformation ScriptName port. The script file identify ought to comprise an entire path to the script file. The SQL transformation acts as passive transformation in script mode and returns one row for every enter row. The output row accommodates outcomes of the question and any database error.

SQL Transformation default ports in script mode

In script mode, By default three ports will probably be created in SQL transformation. They are

  • ScriptName (Input port) : Receives the identify of the script to execute for the present row.
  • ScriptConsequence (output port) : Returns PASSED if the script execution succeeds for the row. Otherwise FAILED.
  • ScriptError (Output port) : Returns errors that happen when a script fails for a row.

Rules and Guidelines for script mode

You must comply with the beneath guidelines and pointers when utilizing the sql transformation in script mode:

  • You can run solely static sql queries and can’t run dynamic sql queries in script mode.
  • You can embody a number of sql queries in a script. You must separate every question with a semicolon.
  • The integration service ignores the output of choose statements in the SQL scripts.
  • You can not use procedural languages similar to oracle plsql or Microsoft/Sybase T-SQL in the script.
  • You can not name a script from one other script. Avoid utilizing nested scripts.
  • The script should be accessible to the combination service.
  • You can not cross arguments to the script.
  • You can use mapping variables or parameters in the script file identify.
  • You can use static or dynamic database connection in the script mode.

Note: Use SQL transformation in script mode to run DDL (information definition language) statements like creating or dropping the tables.

Create SQL Transformation in Script Mode

We will see the best way to create sql transformation in script mode with an instance. We will create the next gross sales desk in oracle database and insert information into the desk utilizing 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 gross sales values(1,'Samsung',2000);
Insert into gross sales values(2,'LG',1000);
Insert into gross sales values(3,'Nokia',5000);

I created two script recordsdata in the $PMSourceFileDir listing. The sales_ddl.txt accommodates the gross sales desk creation assertion and the sales_dml.txt accommodates the insert statements. These are the script recordsdata to be executed by SQL transformation.

We want a supply which accommodates the above script file names. So, I created one other file in the $PMSourceFileDir listing to retailer these script file names.

File identify: $PMSourceFileDir/Script_names.txt

> cat $PMSourceFileDir/Script_names.txt
$PMSourceFileDir/sales_ddl.txt
$PMSourceFileDir/sales_dml.txt

Now we’ll create a mapping to execute the script recordsdata utilizing the SQL transformation. Follow the beneath steps to create the mapping.

  • Go to the mapping designer instrument, supply analyzer and create the supply file definition with the construction because the $PMSourceFileDir/Script_names.txt file. The flat file construction is proven in the beneath picture.
  • Go to the warehouse designer or goal designer and create a goal flat file with end result and error ports. This is proven in the beneath picture.
  • Go to the mapping designer and create a brand new mapping.
  • Drag the flat file into the mapping designer.
  • Go to the Transformation in the toolbar, Create, choose the SQL transformation, enter a reputation and click on on create.
  • Now choose the SQL transformation choices as script mode and DB sort as Oracle and click on okay.
  • The SQL transformation is created with the default ports.
  • Now join the supply qualifier transformation ports to the SQL transformation enter port.
  • Drag the goal flat file into the mapping and join the SQL transformation output ports to the goal.
  • Save the mappping. The mapping circulation picture is proven in the beneath image.
  • Go to the workflow supervisor, create a brand new mapping and session.
  • Edit the session. For supply, enter the supply file listing, supply file identify choices as $PMSourceFileDir and Script_names.txt respectively. For the SQL transformation, enter the oracle database relational connection.
  • Save the workflow and run it.

This will create the gross sales desk in the oracle database and inserts the information.

Recommended Reading:


Joiner Transformation in Informatica
Informatica Scenario Based Questions
Rank Transformation in Informatica
Aggregator Transformation in Informatica
Source Qualifier Transformation in Informatica

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply