Putting reusable code into SSIS components....

If I have standard code for logging, event handling, error handling, etc...How to do you get the standard SSIS components to include this code...

I would hate to have to add this standard code, each time I add a component to my package...especialy if I have to build hundreds of them...

Thx in advance...

RC

You have a few options. The 2 worth considering are:

1) Write a custom log provider
2) Build your custom logging/error handling into eventhandlers.

2) is, I would say, easier. This demo may help: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
Oh, and don't forget about the ability to create template packages. Your template can contain all the logging/error handling functionality that you need which means you only have to define it once. See here for more on templates: http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx

Trust me, you don't have to define all this stuff every time you add a component/task.

-Jamie|||Note that BOL has a topic "Creating a Custom Log Provider in Integration Services" that has enough code to get you going. However at RTM there was not a separate standalone custom log provider code sample.

-Doug
|||What I've done is put source code for event handlers into a source control system, and then copy & paste them into every event handler box on every component. I admit, this is a TERRIBLE solution, but I've not had time to develop a XSLT solution -- the only decent solution I can think of is some XSLT to automate this terrible copy & paste. Plus I've feared automatically changing dtsx files, as they've changed through the beta & CTP cycle, and they seem to undergo many changes even with very small changes by a human.

Has anyone else solved this, or written XSLT they'd care to share, to automate the copy & paste into many copies of event handlers?

Granted, it would be nice if SSIS supported reusability, but, it seems to lack reusability at other levels as well (especially Derived Column expressions are a real problem lacking reusability), so I don't know how likely it is that they'll address this any time soon.
|||

Perry_Rapp wrote:

What I've done is put source code for event handlers into a source control system, and then copy & paste them into every event handler box on every component. I admit, this is a TERRIBLE solution, but I've not had time to develop a XSLT solution -- the only decent solution I can think of is some XSLT to automate this terrible copy & paste. Plus I've feared automatically changing dtsx files, as they've changed through the beta & CTP cycle, and they seem to undergo many changes even with very small changes by a human.

Has anyone else solved this, or written XSLT they'd care to share, to automate the copy & paste into many copies of event handlers?

Granted, it would be nice if SSIS supported reusability, but, it seems to lack reusability at other levels as well (especially Derived Column expressions are a real problem lacking reusability), so I don't know how likely it is that they'll address this any time soon.

Reusability will be addressed in a big way in the next version I am quite sure of that!

If you are logging to a database table then you could write a sproc to do the logging and call that from wherever you need to.

What I do is have a "master" package in which all my logging is configured. Then from that I call the packages that actually do the work. I pass in an ID and the "master" package looks up in a metadata DB which package(s) it needs to call for that ID.

Also be aware that you can build template packages that contain all your logging. http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx

-Jamie