: What is the correct way of utilizing the “When No Data Found” field in the SQL Task?
: The SQL Task can connect to a database server for running SQL statements, which is useful for retrieving rows (records) from tables (physical files) or performing updates, inserts, etc. The database server connections can be pre-defined as reusable Resources, which can be selected from a drop-down menu in the SQL Task.
The Query Element of a SQL Task allows you to specify a SQL Statement that will execute on your SQL server. The required element for the Query Element is the SQL Statement to be executed. Any valid SQL statement that is supported by the target database server is allowed. Some example statement types include – SELECT, INSERT, UPDATE, DELETE, CALL, CREATE, etc. An easy to use graphical tool is provided to build SELECT statements. Click on the ‘..’ button to launch the SQL Wizard.
: Not all databases support closing semi-colons and, as a result, may return an invalid character error.
The “When No Data Found” field is in the Advanced Panel of the Query Element. Here, you specify the action to take when the query produces an empty RowSet.
By default, the project will continue
with executing the next task. Note that this attribute does not have any effect for SQL statements that DO NOT produce a RowSet such as insert or update statements.
Another option for this field is to throw an error
and stop the current execution.
The final option is to create or replace a specified project variable
with the specified value and continue on with the next task or module; for example, setVariable:error=true. So, if the query produces an empty RowSet, then this error variable will be given the value of “true”. It is advised to create a variable before making this query so that if there are no empty RowSets, there is still an existing variable to use for the Project.