THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Querying the SSIS Catalog? Here’s a handy query!

I’ve been working on a SQL Server Integration Services (SSIS) solution for about 6 months now and I’ve learnt many many things that I intend to share on this blog just as soon as I get the time. Here’s a very short starter-for-ten…

I’ve found the following query to be utterly invaluable when interrogating the SSIS Catalog to discover what is going on in my executions:

SELECT event_message_id,MESSAGE,package_name,event_name,message_source_name,package_path,execution_path,message_type,message_source_type
FROM   (
SELECT  em.*
FROM    SSISDB.catalog.event_messages em
WHERE   em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
event_name NOT LIKE '%Validate%'
/* Put in whatever WHERE predicates you might like*/
--WHERE event_name = 'OnError'
--WHERE package_name = 'Package.dtsx'
--WHERE execution_path LIKE '%<some executable>%'
ORDER BY message_time DESC


Know it. Learn it. Love it.


Published Wednesday, October 17, 2012 2:05 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Uri Dimant said:

Thank you for great info... How critical is to have a backup of SSISDB database?

October 21, 2012 3:55 AM

jamiet said:

Just like any database the need to back it up depends on the criticality of the data therein. Its up to you to decide how critical you think that data is. What would the impact be if you lost it? The answer to that question differs for everyone hence the answer to your question is "it depends".

October 22, 2012 8:42 AM

mbourgon said:

Jamie, do you have a good way to email failure messages when a job tanks?  Currently we get the "look in the dashboard" from the notification email, which is awesome advice at 2am on a blackberry.  

Am digging through Job Agent Tokens along with querying SSISDB.  The queries that SSMS runs for the standard MS dashboard appear flawed - the one that starts "WITH conns as" returns 4 records for a failed job, but SSMS dashboard (not yours, the standard MS) returns 1.


November 2, 2012 4:31 PM

Traian said:

Thanks a lot Jamie, I was trying to figure out why a pack failed and the reports were lagging so I needed something quick to query the SSISDB, this worked perfectly!

February 25, 2014 9:13 AM

Dan Thompson said:

mbourgon - OnError event handler and use the variable System.ErrorDescription. You could log it to a table and then send an email with the errors at the end (if there are any) or just send an email on each error.

March 19, 2014 12:06 PM

pug said:


Is there a way to query the internal. tables and get the xml (package definition). In the old version we could do that by querying ssiscatalog in msdb but haven't figured out in 2012 which table in ssisdb the package definition is stored.


April 9, 2014 12:53 PM

jamiet said:

Hi pug,

Great question. I tried to do that once, but failed. Unfortunately (and you'll hate me for this) I didn't document the reason *why* it failed and I'm darned if I can remember.

Matt Masson might be your man if you can get hold of him.



April 11, 2014 4:11 PM

Naveen said:

Hi Jamie,

I am not finding SSISDB in my database engine, how to have that, please suggest on this

December 15, 2014 9:56 AM

jamiet said:

Hi Naveen,

I'll need more info before I can answer that. What version of SQL Server do you have installed?

December 15, 2014 10:05 AM

Raj said:

Hi Jamie,

Fantastic article. I which table can I find the Executable-Type ? For e.g. if the executable is a DFT or a ESql etc ?

January 4, 2015 1:20 PM

Raj said:

Hi Jamie,

Fantastic article. I which table can I find the Executable-Type ? For e.g. if the executable is a DFT or a ESql etc ?

January 4, 2015 1:20 PM

Tamir Gerber said:

Hi Jamie,

It's very useful article!

I just want to add some extra for BIG projects.

In my case all I was requested to show was the time (start and finish) and the work (tables and record counters).

So that was my query:


operation_id ExecutionID,

source_name SourceName,

replace(package_name, '.dtsx', '') PackageName,

message Details




o.object_name source_name,



SSISDB.catalog.event_messages em

INNER JOIN SSISDB.catalog.operations o on em.operation_id=o.operation_id


em.operation_id=(SELECT max(em1.operation_id) FROM SSISDB.catalog.operations o1 INNER JOIN SSISDB.catalog.event_messages em1 ON o1.operation_id=em1.operation_id WHERE o1.object_name=@sSourceName)


event_name NOT LIKE '%Validate%'



message LIKE '% wrote %'





(message like '%Start%' OR message like '%Finished%')



) Q






February 17, 2015 6:40 PM

Jie said:

Thank you for sharing.

July 7, 2015 11:52 AM

Neil said:

Thanks for this script, I must be using this on a daily basis and its far quicker than waiting for the reports to return results :-)

October 15, 2015 4:23 AM

Ade said:

kindly share what database you ran this query against

October 15, 2015 6:50 AM

Ade said:


Sorry to bother with that query, It isn't meant for this forum.

But great job with the query you also supplied above for querying SSIS catalog

October 15, 2015 6:52 AM

Subrat said:


Should we shut down SysSSISLogs when SSIS Catalog logging  is available , what is the difference between the two ?



April 12, 2016 9:39 AM

Susan said:


Is there any way to query the package definition or data from ssisdb?

June 16, 2016 7:07 PM

Leave a Comment


This Blog


Privacy Statement