Contents
hide
Introduction
In this post, you will learn how to Monitor and Run SSIS Package using Stored Procedure (T-SQL Code) – Packages stored in SSIS Catalog. Calling SSIS Packages via T-SQL / Stored procedure code can be useful if you want to trigger SSIS package execution from your SQL code on certain conditions (i.e. Load data from SSIS Package before executing certain SQL code).
Techniques mentioned in this article applies to SSIS PowerPack or Native SSIS Packages. Now let’s look at each piece step-by-step.
How to run SSIS package using Stored Procedure / T-SQL Code
Let’s look at simple example of Calling SSIS package deployed to SSIS Catalog. Basically, there are three steps (setting parameters is optional).
- Required – Call catalog.create_execution System stored proc – This Creates new execution and returns you execution_id which can be saved to a variable
- Optional – Call catalog.set_execution_parameter_value System stored proc – Call this multiple times if you want to set parameters
- Required – Call catalog.start_execution to execute the package
Now let’s look at the example. Below example is the simplest way to call SSIS package using SQL code. Below code creates a new SSIS execution and returns immediately without waiting to finish package execution (asynchronous execution).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB go declare @my_execution_id bigint declare @my_folder_name varchar(255)= 'MyFolder' declare @my_project_name varchar(255) = 'MyProject' declare @my_package_name varchar(255) = 'MyPackage.dtsx' --//////////////////////////////////////////////////////// --First create Execution and get ExecutionID in a variable --//////////////////////////////////////////////////////// exec catalog.create_execution @folder_name = @my_folder_name ,@project_name = @my_project_name ,@package_name = @my_package_name ,@reference_id = null ,@execution_id = @my_execution_id output --//////////////////////////////////////////////////////// --Start Execution --//////////////////////////////////////////////////////// exec catalog.start_execution @my_execution_id |
How to wait until SSIS package is finished (Set SYNCHRONIZED)
By default SSIS Catalog Package execution is in asynchronous mode. So what if you really want to wait until SSIS package execution is done (Synchronous mode) ? Just add one more sored procedure call before calling catalog.start_execution (see below)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB go declare @my_execution_id bigint declare @my_folder_name varchar(255)= 'MyFolder' declare @my_project_name varchar(255) = 'MyProject' declare @my_package_name varchar(255) = 'MyPackage.dtsx' --//////////////////////////////////////////////////////// --First create Execution and get ExecutionID in a variable --//////////////////////////////////////////////////////// exec catalog.create_execution @folder_name = @my_folder_name ,@project_name = @my_project_name ,@package_name = @my_package_name ,@reference_id = null ,@execution_id = @my_execution_id output --//////////////////////////////////////////////////////// --Wait until package execution is done --//////////////////////////////////////////////////////// EXEC [catalog].[set_execution_parameter_value] @my_execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1 --//////////////////////////////////////////////////////// --Start Execution --//////////////////////////////////////////////////////// exec catalog.start_execution @my_execution_id |
Set SSIS Package Parameters via SQL Code for execution
Now lets look at common scenario to pass package parameters.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB go declare @my_execution_id bigint declare @my_folder_name varchar(255)= 'ZappySys' declare @my_project_name varchar(255) = 'DownloadFileFromCloud' declare @my_package_name varchar(255) = 'DownloadFileFromCloud.dtsx' --//////////////////////////////////////////////////////// --First create Execution and get ExecutionID in a variable --//////////////////////////////////////////////////////// exec catalog.create_execution @folder_name = @my_folder_name ,@project_name = @my_project_name ,@package_name = @my_package_name ,@reference_id = null ,@execution_id = @my_execution_id output --//////////////////////////////////////////////////////// --Wait until package execution is done --//////////////////////////////////////////////////////// EXEC [catalog].[set_execution_parameter_value] @my_execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1 --//////////////////////////////////////////////////////// --Set package parameters --//////////////////////////////////////////////////////// exec catalog.set_execution_parameter_value @my_execution_id, 50, 'FilePath', 'C:\Temp\Dump.json' exec catalog.set_execution_parameter_value @my_execution_id, 50, 'TargetServerName', 'Server001' --//////////////////////////////////////////////////////// --Start Execution --//////////////////////////////////////////////////////// exec catalog.start_execution @my_execution_id |
How to set SSIS environment for package execution using SQL Query
Many times you have need to select SSIS Environment rather than setting individual SSIS Parameters (e.g. Use DEV Environment parameters). See below code How to find correct reference_id for SSI environment and pass to create_execution stored procedure call.
Here is how you set SSIS environment using SSMS UI. In the next section see the code to select same via T-SQL code.
Set SSIS Environment for Package Execution – SSMS UI
Setting SSIS Environment for execution using SQL Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB go declare @my_execution_id bigint declare @my_folder_name varchar(255)= 'ZappySys' declare @my_project_name varchar(255) = 'DownloadFileFromCloud' declare @my_package_name varchar(255) = 'DownloadFileFromCloud.dtsx' declare @my_environment_name varchar(255) = 'ENV_DEV' --Find Environment reference_id from name declare @my_env_refid int select @my_env_refid=reference_id from [catalog].environment_references where environment_name=@my_environment_name --//////////////////////////////////////////////////////// --First create Execution and get ExecutionID in a variable --//////////////////////////////////////////////////////// exec catalog.create_execution @folder_name = @my_folder_name ,@project_name = @my_project_name ,@package_name = @my_package_name ,@reference_id = @my_env_refid --pass environment id here ,@execution_id = @my_execution_id output --//////////////////////////////////////////////////////// --Wait until package execution is done --//////////////////////////////////////////////////////// EXEC [catalog].[set_execution_parameter_value] @my_execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1 --//////////////////////////////////////////////////////// --Set package parameters --//////////////////////////////////////////////////////// exec catalog.set_execution_parameter_value @my_execution_id, 50, 'FilePath', 'C:\Temp\Dump.json' exec catalog.set_execution_parameter_value @my_execution_id, 50, 'TargetServerName', 'Server001' --//////////////////////////////////////////////////////// --Start Execution --//////////////////////////////////////////////////////// exec catalog.start_execution @my_execution_id |
How to get last SSIS Package execution Status using SQL Query
Now let’s look at, how to get SSIS package execution Status for last execution using SQL Query. In this example we are grabbing last package execution_id using Max(execution_id) function but you can hardcode execution_id too.
See different numeric status codes and description for SSIS Package Execution.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB GO declare @my_execution_id bigint Select @my_execution_id=Max(execution_id) From SSISDB.catalog.executions --Where package_name like 'MyPackage%' select folder_name,project_name,package_name,status_text=case when status=1 then 'Created' when status=2 then 'Running' when status=3 then 'Cancelled' when status=4 then 'Failed' when status=5 then 'Pending' when status=6 then 'Ended Unexpectedly' when status=7 then 'Succedded' when status=8 then 'Stopping' when status=9 then 'Completed' else 'Not Sure' end from catalog.executions where execution_id=@my_execution_id |
How to get last SSIS Package execution log using SQL Query
Now let’s look at full SQL Query which can give us execution status, errors and full execution log of last SSIS Package execution.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB GO declare @my_execution_id bigint Select @my_execution_id=Max(execution_id) From catalog.executions --Where package_name like 'MyPackage%' --//////////////////////////////////////////////// --See just SSIS package execution status --//////////////////////////////////////////////// select folder_name,project_name,package_name,status_text=case when status=1 then 'Created' when status=2 then 'Running' when status=3 then 'Cancelled' when status=4 then 'Failed' when status=5 then 'Pending' when status=6 then 'Ended Unexpectedly' when status=7 then 'Succedded' when status=8 then 'Stopping' when status=9 then 'Completed' else 'Not Sure' end from catalog.executions where execution_id=@my_execution_id --//////////////////////////////////////////////// --See Only Errors --//////////////////////////////////////////////// SELECT top 100 @my_execution_id ExecID,message, package_name, event_name, message_source_name,message_time as start_time FROM (SELECT event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name, event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID, message_code FROM catalog.event_messages AS em WHERE (operation_id =@my_execution_id) AND (event_name NOT LIKE '%Validate%') ) AS q Where q.event_name='OnError' ORDER BY message_time DESC --//////////////////////////////////////////////// --See Full Log (Information, Warnings and Errors --//////////////////////////////////////////////// SELECT top 1000 event_message_id as id , datalength(message) message_length , message , try_cast('<![CDATA[' + message + ']]>' as XML) as message_xml , message , package_name, event_name, message_source_name,@my_execution_id execid,message_time FROM (SELECT event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name, event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID, message_code FROM catalog.event_messages AS em WHERE (operation_id =@my_execution_id) AND (event_name NOT LIKE '%Validate%') --and event_message_id=20303352 ) AS q ORDER BY event_message_id DESC |
Here is the screenshot. Notice how we have message_xml column to see more than 8000 characters from log message in your SSMS output (Grid mode). SSMS can show maximum 8000 characters in one cell so using try_Catch function along with XML datatype will fix that issue.
See SSIS Package Execution log using SQL Query (T-SQL code) – View Execution Status, Error Messages, Information – Also add more than 8000 characters fix for SSMS grid
Full SQL Script (Execute SSIS Package / Monitor SSIS Package)
Here is the full script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
use [SSISDB] GO /* Author: ZappySys - This script demonstrates few aspects of SSIS package execution via code (call system stored procedures) >> How to execute SSIS package via stored procedure call >> How to set package parameters via code >> How to set envirnment via code >> How to monitor SSIS package execution via T-SQL code >> How to check SSIS package execution status / error and execution log - Feel free to modify and comment uncomment certain sections to meet your need. - We are outputting message column in xml format too so you get nice hyper link to see full message. This technique is needed to overcome string length limitation of SSMS */ declare @see_log bit =1 --set it to 0 if you want to execute package rather than see log declare @my_execution_id bigint=null -- enter execution id you like to see. Kepp it null to read last execution declare @my_folder_name varchar(255)= 'AdfExamples' declare @my_project_name varchar(255) = 'SSISNativeExamples' declare @my_package_name varchar(255) = 'HelloWorld.dtsx' declare @my_environment_name varchar(255) = null --e.g. This is SSIS Environment to attach with this execution (e.g. ENV_DEV , ENV_PROD) --declare @my_environment_name varchar(255) = 'ENV_DEV' --declare @my_execution_id bigint=1200 --enter execid to see log declare @starttime datetime declare @statusLoop bit if(@see_log = 1 and @my_execution_id is null) Select @statusLoop=0, @my_execution_id=Max(execution_id) From catalog.executions --WHERE package_name like 'Hello%' if(@my_execution_id is null) begin set @statusLoop=1 declare @my_env_refid int select @my_env_refid=reference_id from [catalog].environment_references where environment_name=@my_environment_name --First create Execution and get ExecutionID in a variable --https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-create-execution-ssisdb-database?view=sql-server-2017 exec catalog.create_execution @folder_name = @my_folder_name ,@project_name = @my_project_name ,@package_name = @my_package_name ,@reference_id = @my_env_refid ,@execution_id = @my_execution_id output --/////////////////////////// --Wait until execution is done --/////////////////////////// -- IF You want to wait until execution is done. If you enable this then no need for status check loop --https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-2017 EXEC [catalog].[set_execution_parameter_value] @my_execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1 set @statusLoop=0 --if you enable this option no need for statucheck --/////////////////////////// --Set Parameters --/////////////////////////// --https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-2017 --exec catalog.set_execution_parameter_value @my_execution_id, 50, 'FilePath', 'C:\Temp\Dump.json' --exec catalog.set_execution_parameter_value @my_execution_id, 50, 'TargetServerName', 'Server001' --/////////////////////////// --Start Execution --/////////////////////////// --https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-start-execution-ssisdb-database?view=sql-server-2017 exec catalog.start_execution @my_execution_id end --/////////////////////////// --Status check loop for wait --/////////////////////////// ---comment these two lines if set SYNCHRONIZED = 1 if(@statusLoop = 1) WHILE @my_execution_id IN (1,2,5,8) WAITFOR DELAY '00:03' -- select folder_name,project_name,package_name,status_text=case when status=1 then 'Created' when status=2 then 'Running' when status=3 then 'Cancelled' when status=4 then 'Failed' when status=5 then 'Pending' when status=6 then 'Ended Unexpectedly' when status=7 then 'Succedded' when status=8 then 'Stopping' when status=9 then 'Completed' else 'Not Sure' end from catalog.executions where execution_id=@my_execution_id --/////////////////// ----Show Just Errors --/////////////////// SELECT top 10 @my_execution_id ExecID,message, package_name, event_name, message_source_name,message_time as start_time FROM (SELECT event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name, event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID, message_code FROM catalog.event_messages AS em WHERE (operation_id =@my_execution_id) AND (event_name NOT LIKE '%Validate%') ) AS q Where q.event_name='OnError' ORDER BY message_time DESC --/////////////////// ----Full Log --/////////////////// SELECT top 1000 event_message_id as id , datalength(message) message_length , message , try_cast('<![CDATA[' + message + ']]>' as XML) as message_xml , package_name, event_name, message_source_name,@my_execution_id execid,message_time FROM (SELECT event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name, event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID, message_code FROM catalog.event_messages AS em WHERE (operation_id =@my_execution_id) AND (event_name NOT LIKE '%Validate%') --and event_message_id=20303352 ) AS q ORDER BY event_message_id DESC |
Conclusion
So finally to conclude, you saw that how easy it is to execute SSIS packages using SQL code, monitor SSIS package execution and extract valuable log via. SSIS PowerPack comes with additional logging features along with standard logging. Try SSIS PowerPack to get additional 70+ new SSIS tasks and components in your SSIS toolbox.
