Post reply

How to migrate a DTS package to SSIS

  • October 16, 2013 at 5:22 am

    #299199

    There are a couple of DTS packages in SQL server 2000.

    Now, the customer is planning to migrate them into SQL Server 2008.

    I would need help in migrating those DTS packages. These are critical production servers and I cannot take any chances.

  • October 16, 2013 at 5:30 am

    #1658844

    There's a DTS package migration wizard:

    Using the DTS Package Migration Wizard

    There's also a 3rd party tool that can help you with the migration:

    DTS xChange

    If it is that mission critical, the cost of the tool is peanuts.

    While migrating, especially look out for ActiveX tasks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 16, 2013 at 7:46 am

    #1658893

    I would say it all depends on how many DTS packages there are.

    1) If there are a few then consider either a migration tool or recreate them in SSIS

    2) If there are a lot consider the fact you can continue to use the DTS packages: http://technet.microsoft.com/en-us/library/ms143755(v=sql.105).aspx

    A number of years back I had the same situation arise. I chose to keep the DTS packages.

    YMMV

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • October 16, 2013 at 9:05 am

    #1658967

    You can still use DTS on 2008 but you would have to plan the migration (assisted by a tool or not) because 2012+ won't support them.

    My advice, don't trust migration tools 100%.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 17, 2013 at 8:05 am

    #1659335

    I had to deal with a very similar scenario and came up with some thoughts on the subject.

    1) If it is not only important, but is urgent (meaning it needs to be done yesterday) or there are a large number of packages to convert...then consider purchasing a tool like Koen suggested. Personally I have had the best experience with DTS xChange as it was much more accurate in converting DTS to SSIS. The main challenges that I ran into were when a DTS package had a significant number of ActiveX script tasks...those don't always convert. In those cases you'll need to touch the SSIS package yourself to fix those pieces. But consider this, if you have a lot of DTS to convert and you can spend some money and get even 80% of the way there (in most cases DTS xChange has a higher conversion rate than that), then it is worth the investment.

    2) If there are only a few packages, then I would convert them myself...especially if you aren't super familiar with SSIS design. Nothing can teach more about something than by doing it. And if you are seriously only talking about a couple DTS to convert, then look at the DTS xChange demo as it gives you 3 free conversions.

    3) If you aren't under the gun for time, but still want the design experience, get the best of both worlds. Take a few packages and convert them manually while you wait for the approval/purchase/etc of the tool you are most interested in.

    And as usual...test test test.

    Greg Goss

    Database/BI Administrator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply