{"id":3460,"date":"2018-05-10T04:14:08","date_gmt":"2018-05-10T04:14:08","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=3460"},"modified":"2018-06-14T14:06:41","modified_gmt":"2018-06-14T14:06:41","slug":"monitor-run-ssis-package-using-stored-procedure-t-sql","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/","title":{"rendered":"Monitor, Run SSIS Package using Stored procedure \/ T-SQL"},"content":{"rendered":"

Introduction<\/h2>\n

In this post, you will learn how to Monitor<\/strong> and Run SSIS Package using Stored Procedure (T-SQL Code) – Packages stored in SSIS Catalog<\/strong>.\u00a0 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).<\/p>\n

Techniques mentioned in this article applies to SSIS PowerPack<\/a> or Native SSIS<\/strong> Packages.\u00a0 Now let’s look at each piece step-by-step.<\/p>\n

How to run SSIS package using Stored Procedure \/ T-SQL Code<\/h2>\n

Let’s look at simple example of Calling SSIS package deployed to SSIS Catalog. Basically, there are three steps (setting parameters is optional).<\/p>\n

    \n
  1. Required –<\/strong> Call catalog.create_execution<\/a> System stored proc – This Creates new execution and returns you execution_id which can be saved to a variable<\/li>\n
  2. Optional –\u00a0<\/strong>Call catalog.set_execution_parameter_value<\/a>\u00a0System stored proc – Call this multiple times if you want to set parameters<\/li>\n
  3. Required –\u00a0<\/strong>Call catalog.start_execution<\/a> to execute the package<\/li>\n<\/ol>\n

    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).<\/p>\n

    USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB\r\ngo\r\ndeclare @my_execution_id bigint\r\n\r\ndeclare @my_folder_name varchar(255)= 'MyFolder'\r\ndeclare @my_project_name varchar(255) = 'MyProject'\r\ndeclare @my_package_name varchar(255) = 'MyPackage.dtsx'\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--First create Execution and get ExecutionID in a variable\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.create_execution \r\n\t@folder_name = @my_folder_name\r\n\t,@project_name = @my_project_name\r\n\t,@package_name = @my_package_name\r\n\t,@reference_id = null\r\n\t,@execution_id = @my_execution_id output\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Start Execution\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.start_execution @my_execution_id<\/pre>\n
    How to wait until SSIS package is finished (Set SYNCHRONIZED)<\/h2>\n
    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\u00a0catalog.start_execution<\/strong> (see below)<\/p>\n
    USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB\r\ngo\r\ndeclare @my_execution_id bigint\r\n\r\ndeclare @my_folder_name varchar(255)= 'MyFolder'\r\ndeclare @my_project_name varchar(255) = 'MyProject'\r\ndeclare @my_package_name varchar(255) = 'MyPackage.dtsx'\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--First create Execution and get ExecutionID in a variable\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.create_execution \r\n\t@folder_name = @my_folder_name\r\n\t,@project_name = @my_project_name\r\n\t,@package_name = @my_package_name\r\n\t,@reference_id = null\r\n\t,@execution_id = @my_execution_id output\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Wait until package execution is done\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nEXEC [catalog].[set_execution_parameter_value] \r\n\t@my_execution_id,  \r\n\t@object_type=50, \r\n\t@parameter_name=N'SYNCHRONIZED', \r\n\t@parameter_value=1\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Start Execution\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.start_execution @my_execution_id<\/pre>\n
     <\/p>\n
    Set SSIS Package Parameters via SQL Code for execution<\/h2>\n
    Now lets look at common scenario to pass package parameters.<\/p>\n
    USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB\r\ngo\r\ndeclare @my_execution_id bigint\r\n\r\ndeclare @my_folder_name varchar(255)= 'ZappySys'\r\ndeclare @my_project_name varchar(255) = 'DownloadFileFromCloud'\r\ndeclare @my_package_name varchar(255) = 'DownloadFileFromCloud.dtsx'\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--First create Execution and get ExecutionID in a variable\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.create_execution \r\n\t@folder_name = @my_folder_name\r\n\t,@project_name = @my_project_name\r\n\t,@package_name = @my_package_name\r\n\t,@reference_id = null\r\n\t,@execution_id = @my_execution_id output\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Wait until package execution is done\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nEXEC [catalog].[set_execution_parameter_value] \r\n\t@my_execution_id,  \r\n\t@object_type=50, \r\n\t@parameter_name=N'SYNCHRONIZED', \r\n\t@parameter_value=1\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Set package parameters\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.set_execution_parameter_value  @my_execution_id, 50, 'FilePath', 'C:\\Temp\\Dump.json'  \r\nexec catalog.set_execution_parameter_value  @my_execution_id, 50, 'TargetServerName', 'Server001'  \r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Start Execution\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.start_execution @my_execution_id\r\n<\/pre>\n
    How to set SSIS environment for package execution using SQL Query<\/h2>\n
    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\u00a0 and pass to create_execution\u00a0<\/strong> stored procedure call.<\/p>\n
    Here is how you set SSIS environment using SSMS UI. In the next section see the code to select same via T-SQL code.<\/p>\n
     <\/p>\n
    \"Set<\/a>
    Set SSIS Environment for Package Execution – SSMS UI<\/p><\/div>\n
     <\/p>\n
    Setting SSIS Environment for execution using SQL Code<\/strong><\/p>\n
    USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB\r\ngo\r\ndeclare @my_execution_id bigint\r\n\r\ndeclare @my_folder_name varchar(255)= 'ZappySys'\r\ndeclare @my_project_name varchar(255) = 'DownloadFileFromCloud'\r\ndeclare @my_package_name varchar(255) = 'DownloadFileFromCloud.dtsx'\r\ndeclare @my_environment_name varchar(255) = 'ENV_DEV'\r\n\r\n--Find Environment reference_id from name\r\ndeclare @my_env_refid int\r\nselect @my_env_refid=reference_id from [catalog].environment_references where environment_name=@my_environment_name\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--First create Execution and get ExecutionID in a variable\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.create_execution \r\n\t@folder_name = @my_folder_name\r\n\t,@project_name = @my_project_name\r\n\t,@package_name = @my_package_name\r\n\t,@reference_id = @my_env_refid --pass environment id here\r\n\t,@execution_id = @my_execution_id output\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Wait until package execution is done\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nEXEC [catalog].[set_execution_parameter_value] \r\n\t@my_execution_id,  \r\n\t@object_type=50, \r\n\t@parameter_name=N'SYNCHRONIZED', \r\n\t@parameter_value=1\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Set package parameters\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.set_execution_parameter_value  @my_execution_id, 50, 'FilePath', 'C:\\Temp\\Dump.json'  \r\nexec catalog.set_execution_parameter_value  @my_execution_id, 50, 'TargetServerName', 'Server001'  \r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Start Execution\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nexec catalog.start_execution @my_execution_id\r\n<\/pre>\n
     <\/p>\n
    How to get last SSIS Package execution Status using SQL Query<\/h2>\n
    Now let’s look at, how to get SSIS package execution Status for last execution using SQL Query.\u00a0 In this example we are grabbing last package execution_id using Max(execution_id) function but you can hardcode execution_id too.<\/p>\n
    See different numeric status codes and description for SSIS Package Execution.<\/p>\n
    USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB\r\nGO\r\ndeclare @my_execution_id bigint\r\nSelect @my_execution_id=Max(execution_id) From SSISDB.catalog.executions \r\n--Where package_name like 'MyPackage%'\r\n\r\n select folder_name,project_name,package_name,status_text=case \r\n\twhen status=1 then 'Created' \r\n\twhen status=2 then 'Running' \r\n\twhen status=3 then 'Cancelled' \r\n\twhen status=4 then 'Failed' \r\n\twhen status=5 then 'Pending' \r\n\twhen status=6 then 'Ended Unexpectedly' \r\n\twhen status=7 then 'Succedded' \r\n\twhen status=8 then 'Stopping' \r\n\twhen status=9 then 'Completed' \r\n\telse 'Not Sure'\r\n\tend\r\nfrom catalog.executions  where execution_id=@my_execution_id\r\n\r\n<\/pre>\n
     <\/p>\n
    How to get last SSIS Package execution log using SQL Query<\/h2>\n
    Now let’s look at full SQL Query which can give us execution status, errors and full execution log of last SSIS Package execution.<\/p>\n
    USE SSISDB -- <<< Change your SSIS Catalog DB name if its other than SSISDB\r\nGO\r\ndeclare @my_execution_id bigint\r\nSelect @my_execution_id=Max(execution_id) From catalog.executions \r\n--Where package_name like 'MyPackage%'\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--See just SSIS package execution status\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n select folder_name,project_name,package_name,status_text=case \r\n\twhen status=1 then 'Created' \r\n\twhen status=2 then 'Running' \r\n\twhen status=3 then 'Cancelled' \r\n\twhen status=4 then 'Failed' \r\n\twhen status=5 then 'Pending' \r\n\twhen status=6 then 'Ended Unexpectedly' \r\n\twhen status=7 then 'Succedded' \r\n\twhen status=8 then 'Stopping' \r\n\twhen status=9 then 'Completed' \r\n\telse 'Not Sure'\r\n\tend\r\nfrom catalog.executions  where execution_id=@my_execution_id\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--See Only Errors\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nSELECT top 100 @my_execution_id ExecID,message, package_name, event_name, message_source_name,message_time as start_time\r\nFROM   (SELECT        \r\n\t\tevent_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name,\r\n        event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID,\r\n        message_code\r\n        FROM            catalog.event_messages AS em\r\n        WHERE   (operation_id =@my_execution_id) \r\n\t\t\tAND (event_name NOT LIKE '%Validate%')\r\n\t) AS q\r\nWhere q.event_name='OnError'\r\nORDER BY message_time DESC\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--See Full Log (Information, Warnings and Errors\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nSELECT top 1000 event_message_id as id\r\n\t, datalength(message) message_length\r\n\t, message\r\n\t, try_cast('<![CDATA[' + message + ']]>' as XML) as message_xml\r\n\t, message\r\n\t, package_name, event_name, message_source_name,@my_execution_id execid,message_time\r\nFROM   (SELECT        \r\n\t\tevent_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name,\r\n        event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID,\r\n        message_code\r\n        FROM            catalog.event_messages AS em\r\n        WHERE   (operation_id =@my_execution_id) \r\n\t\t\tAND (event_name NOT LIKE '%Validate%') \r\n\t\t\t--and event_message_id=20303352\r\n\t) AS q\r\nORDER BY event_message_id DESC<\/pre>\n
    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.<\/p>\n
    \"See<\/a>
    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<\/p><\/div>\n
    <\/h2>\n
    Full SQL Script (Execute SSIS Package \/ Monitor SSIS Package)<\/h2>\n
    Here is the full script.<\/p>\n
    use [SSISDB]\r\n\r\nGO\r\n\r\n\/*\r\n Author: ZappySys\r\n \r\n - This script demonstrates few aspects of SSIS package execution via code (call system stored procedures)\r\n\t>> How to execute SSIS package via stored procedure call\r\n\t>> How to set package parameters via code\r\n\t>> How to set envirnment via code\r\n\t>> How to monitor SSIS package execution via T-SQL code\r\n\t>> How to check SSIS package execution status \/ error and execution log\r\n - Feel free to modify and comment uncomment certain sections to meet your need.\r\n - 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\r\n\r\n *\/\r\n\r\ndeclare @see_log bit =1 --set it to 0 if you want to execute package rather than see log\r\ndeclare @my_execution_id bigint=null  -- enter execution id you like to see. Kepp it null to read last execution \r\n\r\ndeclare @my_folder_name varchar(255)= 'AdfExamples'\r\ndeclare @my_project_name varchar(255) = 'SSISNativeExamples'\r\ndeclare @my_package_name varchar(255) = 'HelloWorld.dtsx'\r\ndeclare @my_environment_name varchar(255) = null  --e.g. This is SSIS Environment to attach with this execution (e.g. ENV_DEV , ENV_PROD)\r\n--declare @my_environment_name varchar(255) = 'ENV_DEV'\r\n\t  \r\n--declare @my_execution_id bigint=1200 --enter execid to see log\r\n\r\ndeclare @starttime datetime\r\ndeclare @statusLoop bit\r\n\r\nif(@see_log = 1 and @my_execution_id is null)\r\n\tSelect @statusLoop=0, @my_execution_id=Max(execution_id) \r\n\tFrom catalog.executions \r\n\t--WHERE package_name like 'Hello%'\r\n\r\n\r\nif(@my_execution_id is null)\r\nbegin\r\n\tset @statusLoop=1 \r\n\t\r\n\tdeclare @my_env_refid int\r\n\tselect @my_env_refid=reference_id from [catalog].environment_references where environment_name=@my_environment_name\r\n\r\n\t--First create Execution and get ExecutionID in a variable\r\n\t--https:\/\/docs.microsoft.com\/en-us\/sql\/integration-services\/system-stored-procedures\/catalog-create-execution-ssisdb-database?view=sql-server-2017\r\n\texec catalog.create_execution \r\n\t  @folder_name = @my_folder_name\r\n\t ,@project_name = @my_project_name\r\n\t ,@package_name = @my_package_name\r\n\t ,@reference_id = @my_env_refid\r\n\t ,@execution_id = @my_execution_id output\r\n\r\n\t --\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\t--Wait until execution is done\r\n\t--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\t -- IF You want to wait until execution is done. If you enable this then no need for status check loop\r\n\t --https:\/\/docs.microsoft.com\/en-us\/sql\/integration-services\/system-stored-procedures\/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-2017\r\n\tEXEC [catalog].[set_execution_parameter_value] \r\n\t\t@my_execution_id,  \r\n\t\t@object_type=50, \r\n\t\t@parameter_name=N'SYNCHRONIZED', \r\n\t\t@parameter_value=1\r\n\tset @statusLoop=0 --if you enable this option no need for statucheck\r\n\t\r\n\t--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\t--Set Parameters\r\n\t--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\t--https:\/\/docs.microsoft.com\/en-us\/sql\/integration-services\/system-stored-procedures\/catalog-set-execution-parameter-value-ssisdb-database?view=sql-server-2017\r\n\t--exec catalog.set_execution_parameter_value  @my_execution_id, 50, 'FilePath', 'C:\\Temp\\Dump.json'  \r\n\t--exec catalog.set_execution_parameter_value  @my_execution_id, 50, 'TargetServerName', 'Server001'  \r\n\r\n\t--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\t--Start Execution\r\n\t--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\t--https:\/\/docs.microsoft.com\/en-us\/sql\/integration-services\/system-stored-procedures\/catalog-start-execution-ssisdb-database?view=sql-server-2017\r\n\texec catalog.start_execution @my_execution_id\r\nend\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n--Status check loop for wait\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n---comment these two lines if set SYNCHRONIZED = 1\r\nif(@statusLoop = 1)\r\nWHILE @my_execution_id IN (1,2,5,8)\r\n\tWAITFOR DELAY '00:03'\r\n--\r\n\r\n select folder_name,project_name,package_name,status_text=case \r\n\twhen status=1 then 'Created' \r\n\twhen status=2 then 'Running' \r\n\twhen status=3 then 'Cancelled' \r\n\twhen status=4 then 'Failed' \r\n\twhen status=5 then 'Pending' \r\n\twhen status=6 then 'Ended Unexpectedly' \r\n\twhen status=7 then 'Succedded' \r\n\twhen status=8 then 'Stopping' \r\n\twhen status=9 then 'Completed' \r\n\telse 'Not Sure'\r\n\tend\r\nfrom catalog.executions  where execution_id=@my_execution_id\r\n\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n----Show Just Errors\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nSELECT top 10 @my_execution_id ExecID,message, package_name, event_name, message_source_name,message_time as start_time\r\nFROM   (SELECT        \r\n\t\tevent_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name,\r\n        event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID,\r\n        message_code\r\n        FROM            catalog.event_messages AS em\r\n        WHERE   (operation_id =@my_execution_id) \r\n\t\t\tAND (event_name NOT LIKE '%Validate%')\r\n\t) AS q\r\nWhere q.event_name='OnError'\r\nORDER BY message_time DESC\r\n\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n----Full Log\r\n--\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nSELECT top 1000 event_message_id as id\r\n\t, datalength(message) message_length\r\n\t, message\r\n\t, try_cast('<![CDATA[' + message + ']]>' as XML) as message_xml\r\n\t, package_name, event_name, message_source_name,@my_execution_id execid,message_time\r\nFROM   (SELECT        \r\n\t\tevent_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name,\r\n        event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID,\r\n        message_code\r\n        FROM            catalog.event_messages AS em\r\n        WHERE   (operation_id =@my_execution_id) \r\n\t\t\tAND (event_name NOT LIKE '%Validate%') \r\n\t\t\t--and event_message_id=20303352\r\n\t) AS q\r\nORDER BY event_message_id DESC<\/pre>\n
    Conclusion<\/h2>\n
    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<\/a> in your SSIS toolbox.<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    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.\u00a0 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 […]<\/p>\n","protected":false},"author":1,"featured_media":3465,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[12,4],"yoast_head":"\r\nMonitor, Run SSIS Package using Stored procedure \/ T-SQL | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to run SSIS Package using Stored procedure \/ T-SQL. Also find out how to pass SSIS parameters, set environment and view execution log using T-SQL\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Monitor, Run SSIS Package using Stored procedure \/ T-SQL | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to run SSIS Package using Stored procedure \/ T-SQL. Also find out how to pass SSIS parameters, set environment and view execution log using T-SQL\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2018-05-10T04:14:08+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2018-06-14T14:06:41+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"737\" \/>\r\n\t<meta property=\"og:image:height\" content=\"463\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"12 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/\",\"url\":\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/\",\"name\":\"Monitor, Run SSIS Package using Stored procedure \/ T-SQL | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png\",\"datePublished\":\"2018-05-10T04:14:08+00:00\",\"dateModified\":\"2018-06-14T14:06:41+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to run SSIS Package using Stored procedure \/ T-SQL. Also find out how to pass SSIS parameters, set environment and view execution log using T-SQL\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png\",\"width\":737,\"height\":463,\"caption\":\"Set SSIS Environment for Package Execution - SSMS UI\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Monitor, Run SSIS Package using Stored procedure \/ T-SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Monitor, Run SSIS Package using Stored procedure \/ T-SQL | ZappySys Blog","description":"Learn how to run SSIS Package using Stored procedure \/ T-SQL. Also find out how to pass SSIS parameters, set environment and view execution log using T-SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/","og_locale":"en_US","og_type":"article","og_title":"Monitor, Run SSIS Package using Stored procedure \/ T-SQL | ZappySys Blog","og_description":"Learn how to run SSIS Package using Stored procedure \/ T-SQL. Also find out how to pass SSIS parameters, set environment and view execution log using T-SQL","og_url":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2018-05-10T04:14:08+00:00","article_modified_time":"2018-06-14T14:06:41+00:00","og_image":[{"width":737,"height":463,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/","url":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/","name":"Monitor, Run SSIS Package using Stored procedure \/ T-SQL | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png","datePublished":"2018-05-10T04:14:08+00:00","dateModified":"2018-06-14T14:06:41+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to run SSIS Package using Stored procedure \/ T-SQL. Also find out how to pass SSIS parameters, set environment and view execution log using T-SQL","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/05\/ssis-package-execution-set-environment.png","width":737,"height":463,"caption":"Set SSIS Environment for Package Execution - SSMS UI"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/monitor-run-ssis-package-using-stored-procedure-t-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Monitor, Run SSIS Package using Stored procedure \/ T-SQL"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3460"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=3460"}],"version-history":[{"count":6,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3460\/revisions"}],"predecessor-version":[{"id":3497,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/3460\/revisions\/3497"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3465"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=3460"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=3460"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=3460"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}