As some of you may know I've been writing about SSIS on this blog for a lon-n-n-n-n-g time now. Too long it seems sometimes. In all that time though I've only had one short client engagement using the product which means that my real-world experience is somewhat limited. Studying something is all well and good but there's nothing quite like "doing it for real".
Well to that end I started on my first full time SSIS engagement recently. I've spent most of my time so far putting together our ETL Strategy which involves coming up with our mechanism for logging, source system access standards, best practices etc... all that sort of stuff. Today though was my first day proper of full on development and already I've learnt loads about the product that I'm going to try and share a little of here. They're only small things but the whole is definately greater than the sum of the parts in this case.
So onwards...
I wanted a way of verifying that I was able to connect to a data source before I actually attempt to pull data from it. Why did I want to do this? Well there are a few reasons:
1) Verify at the start of the package that all data sources are available. This saves on the package failing further down the line after lots of ultimately unnecassary processing
2) Gain some insight into exactly why the data source is unavailable rather than the less than useful messages that SSIS can sometimes give you.
3) Throw custom errors pertianing to the exact cause of the failed connection.
4) Verify the connection string of the connection manager used at runtime. This may not be the same as that set at design-time if it is affected by a configuration or an expression.
5) Conditional workflow based on the cause of the failure
As usual I turned to the trusty Script Task to enable me to do this. Sure a custom task would have been better, and that's definately the next step, but for now I'll stick with the script task. It makes demoing this easier as well!
Here's an example that I've put together. It connects to a Connection Manager called "AdventureWorks" and passes out some useful data depending on the success thereof. The real trick is in the call to the AcquireConnection() method of the connection manager. Take a look.
So let's go through the steps here:
1) Get a reference to the connection manager in question and its connection string
2) Try to acquire a connection to it
3) If the connection succeeds fire some OnInformation events that will be picked up by your logging mechanism.
4) If the connection fails fire some OnError events with some useful debugging information that will be picked up by your logging mechanism.
5) Er...that's it.
Dead easy isn't it? Wanna see it in action. Here's the output from a successful connection
You can see those custom log messages in the output window right? OK, how about if it fails:
You can see its failed pretty easily so straightaway you can add an OnFailure precedence constraint to this task and do something to handle the error. You also get the custom error messages including the failed connection string so you can debug right here as soon as the package has finished executing.
What really makes this powerful is the ability to output your own custom error messages so you can see right where the problem lies. This is invaluable if you're using an expression on your connection string so you can see exactly what it has come up with!
You can take this a step further if you want and loop over all the connection managers in your package trying them out one by one. Here's the code to do that.
One caveat with this though. If you've got alot of connection managers in your package then this could take some time so make sure you adjust the timeout property of your connection managers accordingly.
One important other thing you need to be aware of is this. Make sure you have DelayValidation=TRUE set on your tasks that use the connection managers otherwise your package will fail before it even reaches your verification script task.
So that's it! Comments are welcome. Do you think this is a good/bad/indifferent idea? Why do/don't you like it? Come on...I wanna know!
-Jamie
P.S. Have a go at running this for yourself if you like: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20051010VerifyDataSource.zip