Stored Procedure task
The Stored Procedure task would be used for the following scenarios:
- To use existing SQL Server Stored Procedures
- To use new SQL Server Stored Procedures that have been created to perform transformation of data from a staging area to destination fact and dimension tables.
From the task type screen, select Stored Procedure.
Task Name
Enter a unique task name here. Task names must be unique to a project.
Connection for SQL
Select a Connection for SQL. Predefined connections will be listed here. This defines the connection to the database where the stored procedure is located.
Tip: Only SQL Server connections will be available for selection from the drop down list for this task.
Stored Procedure Name
Specify the stored procedure you want to add to the task by either entering the stored procedure name into the text field or select it by clicking on Procedure Browser. Loome Integrate can only execute stored procedures on the selected SQL Server database connection.
Once you have selected the stored procedure from the drop down, click Add Selected.
Tip: The stored procedure name must be in the fully qualified name format of
Last End Point
Primarily used to display Data Currency and to support incremental processing. This is a read only field. The last end point indicates the cuff off point is for the data source that the SSIS package is referring to. This could be a time stamp, transaction ID, row number etc.
Connection for Incremental
Select a connection for incremental from the drop down if applicable. All predefined connections will be listed in the drop down list.
Refer to Table Names, Incremental Data and Index Migration for more information on incremental configuration.
Advanced Settings
Toggle to show or hide the advance settings. Advanced settings are different for each task type.
Subject Areas:
Select Subject Area for the task if applicable. Refer to Using a Subject Area for more information.
Parameters:
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 Stored Procedure task you have the option to add a parameter. This section allows variables to be set during execution of the Store Procedure.
The parameter name is the name defined as a SQL Server Stored Procedure variable (E.G. @State VARCHAR(20)).
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 you have created the task, follow the instructions for Configuring Parameters and Dependency to assign a parameter value to the task. NOTE: Stored Procedure 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.
Logging:
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 Stored Procedure task type are:
- Standard - the default logging level as provided by Loome Integrate
- Debug - the standard logging level with additional logging to assist with investigation of issues
- Save Packages - the standard logging level with actual SSIS package to nominated disk location (primarily used for issue resolution)
- Save Packages & Debug Logging - all of the above
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.
Any messages written using PRINT will be logged in the task’s execution history.
Action Buttons
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.