The SQL Statement task would be used for the following scenarios:
From the task type screen, select SQL Statement.
Enter a unique task name here. Task names must be unique to a project.
Select a SQL connection from the drop down list. Predefined connections will be listed here. This is the connection to the database you are running the SQL command against.
Tip: The connection must be created first before you can select it from the drop down.
Enter either the SQL command or a UNC path here. Click Verify to validate the command/ path.
Tip: Create and execute the desired script in SSMS before copying and pasting it into Loome Integrate. This will allow for a successful execution of the command.
Toggle to show or hide the advance settings. Advanced settings are different for each task type.
Select Subject Area for the task if applicable. Refer to Using a Subject Area for more information.
Adding a parameter in Loome Integrate is a 2 step process. The parameter names are created in the task details screen and subsequently the parameter values are assigned in the Parameters And Dependency screen. Here we are just adding the name of the parameter. No value has been assigned to this parameter.
For a SQL Statement task you have the option to add a parameter. This section allows variables to be set during execution of the SQL command/ UNC path to SQL File.
Enter the Parameter Name and click Add Parameter. This will create a list below the field. You can continue adding more parameter names if required. When you have added a parameter name, it will appear in the parameters list.
Once the parameters have been defined in Loome Integrate, the parameter must also exist in the SQL command/ UNC path to SQL File. Do this by prefixing the parameter name with the ‘@’ symbol in the script.
Once you have created the task, follow the instructions for Configuring Parameters and Dependency to assign a parameter value to the task.
NOTE: SQL Statement tasks supports a SELECT statement as parameter value.
Tip: The parameter values are set in a different place to where it is created.
This is to allow for the same task to be added to a job (or multiple jobs) with different parameter values passed each time it is run.
You can choose to select task logging.
Select a logging level. Logging options vary depending on the task type selected.
The available logging options for a SQL Statement task type are:
Tip: If you have both job logging and task logging on- when job logging is set to Standard then the task logging option will override this. Otherwise job logging will always take precedence.
When you have entered all the necessary task details, click Save.
An information box will appear to confirm that the task has been successfully saved. Click Close.
Tip: New tasks are added to the bottom of the list as Enabled.