Late last week I was informed our new servers would be available January 3, and I should be ready to set up my new SQL 2008R2 two-node cluster. I can’t begin to tell you how excited I am about this project! I’d already downloaded the latest backups for my databases, and restored them to my 2008R2 Development environment.
As expected, all of them restored without a hitch. I’d already checked out the Profiler trace looking for deprecated features being used in my databases, and I found none of any significance. I started porting over the jobs and SSIS packages to my development environment. That’s when I received a late Christmas present.
I found that I had thirteen SSIS packages that were just wrappers for DTS packages.
Awesome! Just what I wanted (doing my best to keep sarcasm out of my voice…and failing miserably)!
So I had to dig into DTS. But I had no instance of SQL 2000 running. That’s when I found Pragmatic Works DTS xChange. It promised nearly hands free conversion for my DTS packages into SSIS. It would even create SSIS 2008 packages.
I had to give it a go. I like the fact that they set up their software for the whole project of converting DTS packages to SSIS. It would have been easy to jump straight into the actual conversion process. They have a calculator that can help you justify the cost of the software by computing the cost of converting the DTS package by hand.
It appeared that if I converted more than 3 packages, the software would show a positive return on investment.
Next, the actual conversion. You could apply rules to the conversion. You can tie into a central auditing framework, a central logging location (either on the SQL server in a table, or to .log files). All of this would have been wonderful, if my packages had been constructed in a logical manner.
Rather than using config files to control sources and destinations, the original designers use ActiveX controls to set and retrieve those settings.
Rather than auditing to one table, or even one database, most ignored auditing, and the rest each had unique logging/auditing methods.
None of the files were processed from a single location (or even the same source machine).
None of them agreed on how to handle errors.
None of them agreed on how to archive the files after processing. You couldn’t even pass in the archive location via config file.
Based on my findings, I’m going to have to push for a complete redesign from scratch. In the mean time, I will be able to use my current SSIS wrappers for the DTS packages in production. But I will have to move relatively quickly to upgrade these packages. I highly suggest you try out this software before you jump straight into the conversion process, at the very least, use it to see where you are, and where you need to go.
For now, I’m going to throw myself into setting up a central auditing location for my processes, and build these packages on paper…then I’ll get into the conversion. Luckily Pragmatic Works gave me a heads up on this process!