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

Messages do not always appear in [catalog].[event_messages] in the order that they occur [SSIS]

This is a simple heads up for anyone doing SQL Server Integration Services (SSIS) development using SSIS 2012. Be aware that messages do not always appear in [catalog].[event_messages] in the order that they occur, observe…

In the following query I am looking at a subset of messages in [catalog].[event_messages] and ordering them by [event_message_id]:

SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM   [catalog].[event_messages] em
WHERE  [event_message_id] BETWEEN 290972 AND 290982
ORDER  BY [event_message_id] ASC
--ORDER BY [message_time] ASC

image

Take a look at the two rows that I have highlighted, note how the OnPostExecute event for “Utility GetTargetLoadDatesPerETLIfcName” appears after the OnPreExecute event for “FELC Loop over TargetLoadDates”, I happen to know that this is incorrect because “Utility GetTargetLoadDatesPerETLIfcName” is a package that gets executed by an Execute Package Task prior to the For Each Loop “FELC Loop over TargetLoadDates”:

image

If we order instead by [message_time] then we see something that makes more sense:

SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM   [catalog].[event_messages] em
WHERE  [event_message_id] BETWEEN 290972 AND 290982
--ORDER BY [event_message_id] ASC
ORDER  BY [message_time] ASC

image

We can see that the OnPostExecute for “Utility GetTargetLoadDatesPerETLIfcName” did indeed occur before the OnPreExecute event for “FELC Loop over TargetLoadDates”, they just did not get assigned an [event_message_id] in chronological order. We can speculate as to why that might be (I suspect the explanation is something to do with the two executables appearing in different packages) but the reason is not the important thing here, just be aware that you should be ordering by [message_time] rather than [event_message_id] if you want to get 100% accurate insights into your executions.

@Jamiet

Published Tuesday, May 15, 2012 5:07 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

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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