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

Posted by jamiet on SQL Blog See other posts from SQL Blog or by jamiet
Published on Tue, 15 May 2012 15:07:00 GMT Indexed on 2012/05/30 16:54 UTC
Read the original article Hit count: 150

Filed under:

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

© SQL Blog or respective owner