One of the great things about working with complex software tools is that no matter how often you use them there is always something you don’t know. Today I learnt such a thing about SQL Server Integration Services (SSIS) courtesy of Doug Laudenschlager’s blog entry Logging custom events for Integration Services tasks.
As Doug says:
All Integration Services tasks and containers support a default set of events for logging. These events typically represent different stages in the "lifetime" of the object at run time, such as "OnPreExecute", "OnPostExecute", and "OnError".
Many tasks also support custom log entries that may be of more interest to you than the generic events.
One of the custom log entries that I didn’t know about was the Execute SQL Task’s ExecuteSQLExecutingQuery and now having taken a look at it I wish I’d have known about it a lot sooner. The real power of this custom log entry is that it shows the SQL statement that gets executed against the data source which is especially valuable if the SQL statement is constructed using an expression.
To demonstrate, here is an execute SQL Task shown at design-time. I have highlighted where an expression is being used to construct a simple SQL statement:
Here I show the resultant log file obtained after executing this package
Three ExecuteSQLExecutingQuery log entries have been created and I’ve highlighted the pertinent one that says Preparing SQL statement:select * from sys.objects, thus proving that the result of the expression gets logged. If your expression is being used to create dynamic SQL (which invariably it will be) then I highly recommend logging the ExecuteSQLExecutingQuery custom log entry.
In case you need to see how to configure logging in order that this custom log entry gets logged I’ve provided a demo package that shows what’s needed. You can download it from my SkyDrive:
-Jamie