SQL Statement
The SQL Statement Task Type is used for executing SQL directly on a connection. Currently the SQL Statement task can only be used with SQL Server connections, supporting T-SQL syntax validation.
Create a SQL Statement Task
Create a new task.
Provide a name to identify this task and choose an agent from the dropdown.
Choose SQL Statement as the task type.
Providing the Statement
In this next section, you can then either enter the script, the file path to the script, or select file/s from a Git Repository.
SQL Statements can be provided directly in-task using the statement editor provided, or the path to a .sql file can be used in place of the statement by choosing File Connection as the Statement Source.
Choose whether the Statement Source will be a script or file connection.
If you chose Script, provide your SQL Statement script or the file path to the script file in the SQL Statements or Filepath field.
Provide the path to the file in Script Folder Path.
Enter the SQL you would like to execute or provide the full file path to a .sql file you would like to execute.
If you chose File Connection, choose your Git Repository from the dropdown and provide your Script Folder Path to select files from a Git Repository.
To learn how to use a File Connection as your Script Source to execute a script in your Git Repository, please read more here.
You can enable a setting for this task to run scripts in parallel. This will allow the Loome Integrate agent to execute multiple scripts in parallel, rather than in sequence. Learn more here.
Utilizing Parameters
SQL Statements support parameters which can be used to provide variables that can be modified at a job sequence level. This means you could have the same SQL Statement task with different results and effects depending on what parameter is provided.
Parameters can be referenced in script using the T-SQL local variable syntax of
@PARAMETER_NAME.
Parameter values need to be set when the task is added to a job’s sequence. Learn more about parameters in the jobs documentation.
You can provide a parameter name and default value in the task. The default value is optional so you do not need to provide a value here.
And then on the job page, click on Edit Parameter Values.
Beside the parameter you added in the task, add a value.
Example Variable Usage
| Param Name | Param Value | Script | Formatted Script |
|---|---|---|---|
| Name | Alex | SELECT * FROM People WHERE [FirstName] = @NAME |
SELECT * FROM People WHERE [FirstName] = 'Alex' |