{"id":752,"date":"2015-11-27T11:18:39","date_gmt":"2015-11-27T05:48:39","guid":{"rendered":"http:\/\/pramodsingla.com\/?p=752"},"modified":"2015-11-27T11:18:39","modified_gmt":"2015-11-27T05:48:39","slug":"ssis-2012-ssisdb-check-execution-status-of-ssis-package","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=752","title":{"rendered":"SSIS 2012 (SSISDB)- Check execution status of SSIS Package"},"content":{"rendered":"

Problem Statement:\u00a0<\/strong>How to check the execution status of \u00a0a SSIS package, deployed in SSISDB, using system tables?<\/p>\n

Solution:<\/strong><\/p>\n

DECLARE<\/span>\u00a0@status<\/span>\u00a0INT<\/i><\/span>=<\/span>2<\/span>\/*Running\u00a0The\u00a0possible\u00a0values\u00a0are\u00a0created\u00a0(1), running\u00a0<\/i><\/span><\/strong><\/p>\n

(2),\u00a0cancelled\u00a0(3), failed\u00a0(4),<\/i><\/span>\u00a0pending\u00a0(5),\u00a0ended\u00a0unexpectedly\u00a0(6),\u00a0succeeded\u00a0(7),\u00a0<\/i><\/span><\/strong><\/p>\n

stopping\u00a0(8),\u00a0and\u00a0completed\u00a0(9)*\/<\/i><\/span><\/strong>
\n SELECT<\/span>\u00a0*<\/span><\/strong>
\n FROM<\/span>\u00a0\u00a0\u00a0catalog<\/span>.<\/span>executions<\/span><\/strong>
\n WHERE<\/span>\u00a0\u00a0status<\/span>\u00a0=<\/span>\u00a0@status<\/span><\/strong><\/p>\n

SELECT<\/span>\u00a0e.executable_id,e<\/span>.<\/span>package_name<\/span>,<\/span><\/strong>
\n e<\/span>.<\/span>[executable_name]<\/span>,<\/span><\/strong>
\n es<\/span>.<\/span>[execution_path]<\/span>,<\/span><\/strong>
\n es<\/span>.<\/span>[execution_duration]<\/span>,<\/span><\/strong>
\n [execution_result]<\/span>\u00a0=<\/span>\u00a0CASE<\/span>\u00a0es<\/span>.<\/span>[execution_result]<\/span><\/strong>
\n WHEN<\/span>\u00a00<\/span>\u00a0THEN<\/span>\u00a0‘Success’<\/span><\/strong>
\n WHEN<\/span>\u00a01<\/span>\u00a0THEN<\/span>\u00a0‘Failure’<\/span><\/strong>
\n WHEN<\/span>\u00a02<\/span>\u00a0THEN<\/span>\u00a0‘Completion’<\/span><\/strong>
\n WHEN<\/span>\u00a03<\/span>\u00a0THEN<\/span>\u00a0‘Cancelled’<\/span><\/strong>
\n END<\/span>,<\/span><\/strong>
\n es<\/span>.<\/span>start_time<\/span>,<\/span><\/strong>
\n es<\/span>.<\/span>end_time<\/span><\/strong>
\n FROM<\/span>\u00a0\u00a0 [SSISDB].[catalog]<\/span>.<\/span>[executables]<\/span>\u00a0e<\/span><\/strong>
\n INNER<\/span>\u00a0JOIN<\/span> [SSISDB].[catalog]<\/span>.<\/span>[executable_statistics]<\/span>\u00a0es<\/span><\/strong>
\n ON<\/span>\u00a0es<\/span>.<\/span>[executable_id]<\/span>\u00a0=<\/span>\u00a0e<\/span>.<\/span>[executable_id]<\/span><\/strong>
\n WHERE<\/span>\u00a0\u00a0e<\/span>.<\/span>execution_id<\/span>\u00a0IN<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0execution_id<\/span><\/strong>
\n FROM<\/span>\u00a0\u00a0\u00a0catalog<\/span>.<\/span>executions<\/span><\/strong>
\n WHERE<\/span>\u00a0\u00a0status<\/span>\u00a0=<\/span>\u00a0@status<\/span>)<\/span><\/strong>
\n ORDER<\/span>\u00a0\u00a0BY<\/span>\u00a0e<\/span>.<\/span>executable_id<\/span>\u00a0DESC,es.start_time DESC<\/span><\/strong><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Problem Statement:\u00a0How to check the execution status of \u00a0a SSIS package, deployed in SSISDB, using system tables? Solution: DECLARE\u00a0@status\u00a0INT=2\/*Running\u00a0The\u00a0possible\u00a0values\u00a0are\u00a0created\u00a0(1), running\u00a0[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[36],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/752"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=752"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/752\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=752"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=752"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=752"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}