THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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)
           AND
event_name NOT LIKE '%Validate%'
      
)q
/* 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

image

Know it. Learn it. Love it.

@jamiet

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

Comments

 

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.

Thanks

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:

Jamie,

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.

Thanks.

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.

Regards

Jamie

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement