SQL Data Check Dynamic Action

About

SQL Data Check uses SQL scripts to validate data in a database. It executes the specified SQL query and evaluates the value returned in the first field of the result.

For example, the user may write the following SQL:

Select count(*) from table

• Successful: if the value of the first field returned by the query is numeric and greater than 0.
• Failed: if the value is 0 or less, or if the first field is not numeric.

This can be used to confirm that expected data exists in the database before continuing with the next processing step.

Metadata tab

1. The Write Variables option ensures that the “Package Action” logs the variable values into the “Action vars before log file” before the action is executed and logs the updated variable values into the “Action vars after log file” after the action completes.
2. This option is typically used for debugging and is not recommended in a production environment.
3. It can be globally disabled through the options dialog .

Parameters tab

• The “Replace Variables” parameter is used to substitute variables within SQL statements.
• When the “UseEmbeddedSQL” is set to True EmbeddedSQL is used, otherwise SQL Script/Lookup is used.

Selecting Script

Editing Script

Selecting Connection

Variables tab

The Variables tab defines which variables are updated during the execution of the action.

Execution log tab

The Execution Log tab is populated after the action has been executed. Double-click a row to view the detailed execution log.

Calculated parameters example

Pascal

begin
 if DayOfWeek(Now)=3 then
    begin
     Result:=1917;
    end
 else
    begin
     Result:=1968;
    end;
end;

Python

import datetime;
if datetime.datetime.today().weekday()==0:
   Result.Value=3917
else:
   Result.Value=3918

Related Actions

1. Sql Script Action
2. Sql Data Check Action
3. Check Connection Action

Useful Links

• Debugging Actions
• Working With Variables

Video Tutorial