In my previous two articles on SQL Server integration Services (SSIS), Parameterizing Database Connection in SSIS and Deploying Packages to SSIS Catalog (SSISDB), packages were developed, deployed and configured in the SSIS Catalog. Now, it is time to execute the packages with various options. There are a couple of ways to do this, but we need to be able to change the parameter values as well as monitor for failures or successes.
The most intuitive execution for a DBA would be to script the T-SQL to execute the package. When we do this, the package is run asynchronous, so it starts and returns quickly to the method used to execute. The T-SQL script uses a status to indicate if the package is executing, has succeeded or has failed along with various other values along the same line.
We can get this script by trying to execute a package deployed to the SSIS Catalog. If you right-click on the package from the SSIS Catalog, there will be a submenu called Execute… like Figure 1.
The Execute Package screen appears where you can change Project Parameter values. The values are retrieved based on the deployed values, assigned environment or the configured values after the project is deployed. Configuration of the changed values can come from one or more Environments that are assigned to the package within the deployed project.
There is a Script button at the top of the execute screen where the text can be saved to a file, the clipboard or displayed in a new query window. Figure 3 shows the script in a new query window after some formatting changes.
The script starts with a declaration of the variable @execution_ID. This is the variable that will hold the status of the executing package while it is running asynchronous. Since no Project Parameters were changed, there is nothing declared or set for this script. The execution will use the SSIS Catalog configured values.
If the above script is executed, it will complete with no errors. The problem is only the SSIS Catalog logging will have the execution status for the running package. Nothing is returned to the execution of the script from the SSIS package. The value can be obtained by creating a loop to check the declared @execution_ID variable.
The possible values are:
- running
- created
- canceled
- failed
- pending
- ended unexpectedly
- succeeded
- stopping
- completed
The loop would need to check the value of @execution_ID and if it is 1, 2, 5 or 8, it is still running. Anything else would be completed. We would need to report a problem if the value ended up as 3, 4 or 6. Figure 4 shows the additional logic for looping to check the completion of the execution
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
WHILE @execution_id IN (1,2,5,8) WAITFOR DELAY '00:01' DECLARE @Msg VARCHAR(MAX) DECLARE @MsgStatus VARCHAR(MAX) SELECT @MsgStatus = CASE WHEN @execution_id IN (1,2,5,8) THEN 'failed' ELSE 'Succeeded' END SET @Msg = 'Package DimCategory completed with a status of: ' + @MsgStatus PRINT @Msg |
These packages were developed in a separate environment than production. So, when we deploy to a production server, the parameter values will be the same as development (or QA). The Environment of the SSIS Catalog is helpful with changing these deployed values. It is also helpful because the Project Parameters are used by multiple Packages in a Project. That is why the switch to Project Parameters is so important along with containing packages in a project deployed to the SSIS Catalog.
Once deployed to the production server, we can create an Environment for the Project. Figure 5 shows where this is done.
First, the Environment has to be created with a name and description. Once that is done, the properties of the Environment can be edited. Figure 6 shows an Environment for changing the staging and production database names as well as the instance name.
Once this is save, we can relate an Environment to a Project. The Environment has to be created in the project’s Environment folder. The Project can be assigned values from the Environment variables to specific Project Parameters like Figure 7.
This enables having the same deployed package run with different values for production versus QA. It also means the project and its packages only have to be deployed to production, if the developer feels ok with that. It does not prevent you from deploying to development, QA and/or production.
You can also setup SQL Server Agent jobs to run with different Environments. Figure 8 shows an agent job with an Environment assigned. The variable names created in the Environment must match the Project Parameter names for this to work in the SQL Server Agent job.
The step of a SQL Server Agent job can have a text file log the information about a success or failure. If you go to the Advanced option in the top left of the Job Step properties, there is an Output file text box and ellipse where you can find a path and enter a filename. Figure 9 shows an option to write the output of the step to a specific location. There is also an option to ‘Append step output to existing file’. If not checked, the file is overwritten when the step is run. Not checking this box appends the output to the same file keeping the history of executions. If you use the append option, the file will grow larger with each run, so be sure to check the size and purge frequently.
Even though we started with scripting T-SQL to launch a package, we saw that assigning the Project an Environment will override the deployed values for Project Parameter(s). The Environment can also be related to an SSIS package as a step in the SQL Server Agent. The Step has a way to log information related to execution and errors.
Side Note
The asynchronous execution can be changed to run synchronous. The syntax below accomplishes making the package run synchronous from a T-SQL script.
|
1 2 3 4 5 6 7 |
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1 |
The parameter value change would make the execution of this package through T-SQL wait to return to caller once the execution finishes. This includes if it fails.
Reference links:
- catalog.create_execution (SSISDB Database)
- Job Step Properties – New Job Step (Advanced Page)
- SSISDB Project Environments
Starting as a developer in COBOL while at LSU, he has been a developer, tester, project manager, team lead as well as a software trainer writing documentation. Involvement in the SQL Server community includes speaking at SQLPASS.org Summits and SQLSaturday since 2011 and has been a speaker at IT/Dev Connections and Live! 360.
Currently, he is the Chair of the PASS Excel Business Intelligence Virtual Chapter and worked on the Nomination Committee for PASS Board of Directors for 2016.
View all posts by Thomas LeBlanc
- Performance tuning – Nested and Merge SQL Loop with Execution Plans - April 2, 2018
- Time Intelligence in Analysis Services (SSAS) Tabular Models - March 20, 2018
- How to create Intermediate Measures in Analysis Services (SSAS) - February 19, 2018