Writing a custom log provider in SSIS

Question

Hi everybody,

I'm currently working with Microsoft SSIS and SQL server 2008 and I would like to write logs to a file using :

• Specific log pattern : Formatted Date - Log Level : Log Message
• Specific log levels
• Error Codes that are associated to error messages in a configuration file

I was wandering on the Web looking for a solution to my problem and I was thinking about writing my own log provider for SSIS.

Does Anybody know how to do it or if it's just a huge waste of time even trying it ?

Thanks for your help

Answer 1

As far as logging SSIS goes:

We use the logging provided by SSIS in a database that contains meta data about all our SSIS packages that was designed by our SSIS expert. This allows us to easily have a corporate standard for what actions are logged and takes advantage of the work Microsoft did to create the logging system. However, the log data is relatively hard to read, so our architect wrote a parser in C# to pull the data into a table with the structure the way we need to see it. So yes, I can tell you it was quite worth the time to do. I can easily read the errors and the steps that were processed for any import. I have an email task that runs on failure that includes a query of the table for that run, so that the support person who has to research the failure can simply run the code in the email to find what happened. This has significantly reduced the time to research and fix errors.

To add further information in response to Nazim's comment: It's a fairly complex database, but it includes things like the type of import/export, the filename, the location of the file to be picked up, the client it is associated with, the frequency, the columns in the file and the type of data it should hold (used for checking if the file is the right format as clients have been known to change them without warning). We use lots of this information as variables in the packages themselves. Then when the package is run, we record the details for that run in a detail table that includes the parsed data and the instanceid (which is populated intially in the first task of each package for each run and is associated with the packageid). Then the last task in the package records the end time and successful status (or if it errors out we have a task that ends the instance with a failed status). Hard to describe as we have over 20 tables in the database and the specific design is proprietary. If you want to design your own, just think of all the data that might be useful as variables, all the data that might be useful for troubleshooting problems, and all the data you might want to have to run managments reports about import and export processes.

Answer 2

I've looked into this a bit, and am of the opinion that its probably better sticking with the in-built SSIS logging capabilities.

If you want to tailor it specifically, have you looked at the possibly logging to a SQL table and then possibly querying this table to in a manner that outputs logs in the format that you require?

Answer 3

As James mentioned above, SSIS has some very nice built in logging capabilities: basic how to on msdn. At my last client, they wanted to log specific information as you do, and in order to do that, we created custom log files by using script tasks in soem of the package event handlers. Some of the more used event handlers are OnPreExecute, OnPostExecute, and OnError. Script tasks allow you access to the .NET libraries which allow you to create the log file and append any information that you deem necessary to it. There is a lot of good information stored in package variables that can be used in your script task such as execution time, user name, start time, etc.

Answer 4

Have you consider Logging framework provided by BI xPress. Its very robust and incldes several canned reports

http://www.pragmaticworks.com/help/bixpress/scr/About%20Auditing%20and%20Monitoring%20Framework.htm

Hope this helps.