Wise man or Wise guy? You Decide

Just another WordPress.com weblog

SSIS Error Logging Custom Component

Posted by sqlwiseguy on August 19, 2008

Well, I finally got around to writing my first custom component for SSIS. It is a synchronous transform component for Error Logging in a DataFlow. I was using a script component, which I wrote about for SQLServerCentral, that I was copying and pasting all over the place and I finally had had enough. Much of the code in the component is based on Josh Twist’s XMLify Custom Component which I found through Jamie Thompson’s excellent SSIS Junkie Blog. I also used the examples in Professional SQL Server 2005 Integration Services, to get through creating the component.

The code itself is pretty easy to follow, if you know anything about SSIS programming ;). It just accepts the input columns (ErrorCode is required, all others optional) and creates the new columns I want to pass out which are:

  1. ErrorDesc – retrieved using the ErrorCode passed in.
  2. ErrorTask – the Data Flow which hosts the component.
  3. ErrorStep – the name of the Error Details component. If anyone knows how to get the name of the previous component let me know as that is really what I’d like to have here.
  4. PackageTime – the start time of the containing package.
  5. ErrorDetails – an xml document consisting of name-value pairs based on the input columns.

Probably one of the neatest things I learned was how to debug the component. I had never used the Debug -> Attach to Process feature of Visual Studio before. It was really cool to start an SSIS Package with a breakpoint in it (never done that before either), then in my Custom Component solution attach to the SSIS process (DtsDebugHost.exe), continue the SSIS package, and then hit breakpoints in my component code. Man, do I LOVE learning new things!

Feel free to use the component and improve on it. I make no warranties on it.

I have posted the code and component in zip format on my new SkyDrive account (link at the bottom of the post). I had to create the SkyDrive account because I can’t put attachments on the blog. The code is also attached to the discussion of the article on SQLServerCentral, here.

Advertisements

5 Responses to “SSIS Error Logging Custom Component”

  1. Ben said

    great component. very useful. i added a few return fields: user name, package name, package ID, and execution ID. the last two allow you to easily join the custom logging table to the built-in logging table, sysdtslog90. now i can combine the output of the two for a more comprehensive logging solution. i’ll share the source code if you like.

  2. Jack said

    Sounds great Ben. I’d love to see the source.

  3. Ben said

    sent you an email

  4. Raman said

    Hi,

    Great post. Just what I was looking for.

    However, I'm a newbie to SSIS. I was wondering whether you could help me implement this component in a SSIS package.

    Thanks.

  5. Jack said

    Raman,

    Check out this post, http://wiseman-wiseguy.blogspot.com/2009/04/deploying-custom-ssis-components.html, for how to install the component. Then you use it just like any other component.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: