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:
- ErrorDesc - retrieved using the ErrorCode passed in.
- ErrorTask - the Data Flow which hosts the component.
- 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.
- PackageTime - the start time of the containing package.
- 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.
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.
ReplyDeleteSounds great Ben. I'd love to see the source.
ReplyDeletesent you an email
ReplyDeleteHi,
ReplyDeleteGreat 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.
Raman,
ReplyDeleteCheck 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.
Ben,
ReplyDeleteNice work, would you like to share the source to me as well.
thanks,
hui,
ReplyDeleteYou could download the source linked in the blog post and add the additional fields yourself. I'll look for the email Ben sent and see if I can get you his source.
hui,
ReplyDeleteIf you sen me an email I can forward you what Ben sent me.
Hi, is it possible to use this component in SSIS 2008. I can't get it to work.
ReplyDeleteI have not tried it with 2008, but Zach Mattson produced an updated version and wrote about it on SQLServerCentral. Here's the article, http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65758/
ReplyDelete