Migrating SQL 2000 DTS to newer versions of sql server
G'day Folks.
This is a general question and not a request for code. I have a client with many DTS packages on their sql server 2000. Has anyone had to migrate these buggers to sql server 2005 or 2008? Was it difficult? - After substantial googling/searching I'm not sure I have my head around what Microsoft have done with this aspect of the system.
It's not difficult if you are already familiar with SSIS. Having experience in .net development will be a big plus as well, as 2005 supports vb.net and 2008 supports both vb.net and c#.
You can actually run your DTS packages under SQL2005 and 2008...There should be a node in the tree in Management Studio that says "Management", under that is "Legacy". Under that you should see another node "Data Transformation Services"...right click on it and you'll that has a Migration Wizard. It sucks, but it's a start. You can also export all the DTS packages from your SQL2000 then using the same nodes and right-click, you can import them into your SQL2008 instance. This works a little better. Then, after importing, if you copy the appropriate DTS dll's and .EXE files (I forget which, but you can google them, I guess) from your SQL2000 tools\binn directory, it will actually allow you to edit your DTS packages, rather than having to convert to SSIS. Keep in mind this is a stopgap...we did this with 300+ DTS packages to "keep the lights on", then took our time to one-by-one convert them to SSIS. Took us about 6 months.
Just a heads-up, legacy mode is about to expire in SQL 11. There's also a 3rd party party component called DTS xChange, which will migrate them pretty seamlessly. This is over at PragmaticWorks.com.
