SQL Script

SQL Script Component (Orchestration)

Write your own custom SQL script.

The script may contain multiple SQL statements, however since there is no way to recover the output of the script the statements should not be SELECT. If you wish to write SELECT queries, the SQL Transformation Component allows for custom SELECT statements where the output can be used as part of the transformation flow.

You should avoid using transaction control statements such as commit or rollback in your scripts. For that, please use the transaction control components in the orchestration job. If you use the statements in a script, Matillion ETL will not be aware of the change in transaction behaviour.


Properties

Property Setting Description
Name Text The descriptive name for the component.
SQL Text The SQL script.

Strategy

Inserts your custom SQL statement.

Example

In this example, we use the SQL Script Component to drop a table named 'matillion_example_table'. This requires an orchestration job such as the one shown below.

The SQL Script component has few properties. It can be named anything you like and the SQL itself is entered into the 'SQL Script' property.

Editing the 'SQL Script' property will bring up the SQL code editor illustrated below. A list of available variables is given on the left side and code can be written on the right. Written code can be executed with the 'Run' button as though the SQL Script component had been run. Any output from the code execution is given beneath the code box. To drop our table, we have written the following script.

DROP TABLE matillion_example_table

And shown below in the component. Running either the job or the script (by hitting the 'Run' button seen below) will execute the script and drop the table.