Skip to content

Product Review: DTS xChange

2010 December 29
by Shannon Lowder

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.

Not only was it coal, but it was ANGRY coal!

Not only was it coal, but it was ANGRY coal!

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!

6 Responses leave one →
  1. college scholarship permalink
    January 11, 2011

    found your site on del.icio.us today and really liked it.. i bookmarked it and will be back to check it out some more later

  2. toyplaza permalink
    January 16, 2011

    Nice post. I have recently been searching for information about this topic and yours is the best I have discovered so far.

  3. ramesh permalink
    August 30, 2013

    Thank you, I am new to this utility and need to use sooner, Need information for this and here are the questions.
    I wanted to know while using DTSxchange what are the components cannot be moved ?
    while in the migration what are the things we should be considered or keep in mind? what are the pro and cons?
    what are easy tasks and what are complex tasks?
    where we need manual intervention or edit needed for packages? sorry too many questions but I need real time hickups. Thanks for help.

    • September 24, 2013

      I haven’t had any problems using DTSxChange for any data flow tasks (take data from source a to destination b). If you have anything more than that, there may be problems. Fortunately DTSxChange will wrap those problem components in a DTS container, and keep those bits in the old code. If that’s a problem (IE you’re using SQL 2012), then you would then have to come up with a manual conversion for that dts package.

      If you’d like to continue the conversation, feel free to reach out through my contact form, and we can set up a time to talk.

Trackbacks and Pingbacks

  1. Tweets that mention ToyboxCreations − Product Review: DTS xChange -- Topsy.com
  2. Michael Coles: Sergeant SQL

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS