Messages do not always appear in [catalog].[event_messages] in the order that they occur [SSIS]
- by jamiet
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] emWHERE [event_message_id] BETWEEN 290972 AND 290982ORDER BY [event_message_id] ASC--ORDER BY [message_time] ASC
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”:
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] emWHERE [event_message_id] BETWEEN 290972 AND 290982--ORDER BY [event_message_id] ASCORDER BY [message_time] ASC
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
