Execute any SQL

When to use this Flow type

In the Etlworks Integrator, it is possible to execute any SQL statement (including DDL) or a list of the ; separated SQL statements outside of the regular source-to-destination transformation methods.

Create Flow

Step 1. Start creating an SQL Flow by creating a database Connection.

Step 2. Continue by opening the Flows window, clicking Add flow, and typing execute SQL in the Select Flow Type box:

Step 3. Select the destination Connection created in step 1.

Step 4. Select the Parameters tab and enter the SQL to execute and other parameters:

Available parameters:

• SQL: SQL to execute. You can enter multiple ; delimited SQL statements. If the SQL is an Anonymous Block Statement, the ; is used as a part of the block and not as a statement separator.
• SQL is Anonymous Block Statement : this means the SQL is an executable statement that can contain control statements, variables, and other SQL statements. It can be used to implement procedural logic in a scripting language. By default, it is assumed that the SQL is not an anonymous block statement, so the parameter must be explicitly set.

An example of the anonymous block statement (TSQL):

• DECLARE 
     @param1 varchar(100);
  BEGIN
     set @param1 = '_xyz';
  
     insert into loop_test (name) 
     values (CONCAT('{name}', @param1));
  END;   
  
  

• Auto Commit: if this option is enabled (the default), the transaction will be automatically committed.
• Script: the JavaScript or Python program that will be executed before executing SQL. You can use it to set the Flow variables that can be referenced as {parameters} in SQL. You can also use it to conditionally disable the SQL Flow.
• On Exception: by default, any error causes the halt of execution. When ignore is selected, the errors will be ignored, and execution will continue. Read how to configure the Flow to ignore all or specific exceptions.
• Exception Mask: you can specify what errors should be ignored and still halt the execution for all other errors. Enter part or all of the exception string. This field works only when the Ignore on Exception option is selected.
• Rollback on Exception: some databases (for example, PostgreSQL) require transaction rollback in case of any exception.
• Execute if Error: if this option is selected, the Etlworks Integrator will execute this SQL if an error occurs. It could be useful if, for example, you want to clean up a database after a failed data integration Flow.