0

I currently have an old SQL server 2000, with some DTS packages in system database msdb, table msdbsysdtspackages.

I need to migrate those packages to sql server 2016, is there a tool I can do that?

if not, is there a way I can know what are those packages doing, so I can at least rewrite them? Because now they all stored as hex data, and I don't even know what are they doing...

Thank you and any advice would be welcome.

  • Have you looked at the dts wizard ? That may help you convert them. I had to do work on DTS a long time ago, so my condolences to you. – Shaulinator Nov 3 '17 at 20:58
  • It is tedious in places, but many people have patched over the long jumps for the migrations. – RLF Nov 4 '17 at 1:03
  • thank you guys, I totally agree. It sucks to work on such old technology, but that's what life is. MY SOLUTION: I found on our old server, we have a tool called SQL Server Enterprise Manager, where under Data Transformation node, I can see all the local packages and design them. Now I'll rewrite them by SSIS on new server, instead of converting them. – Tiancheng Li Nov 6 '17 at 15:15
1

Per all the references I can find (most notably Upgrade Integration Services Packages on MS TechNet, and this StackOverflow answer), the options are:

  • Use the tools available with SQL Server 2005 or 2008 to convert the DTS package to an SSIS package; then, upgrade the SSIS package with SSDT; or
  • Rewrite the package with SSDT, for your current SQL Server version.

Note - in the article, it states that any ActiveX scripts will have to be thrown out. That's not entirely true - you'd need to rewrite them as a script component in SSIS. I believe I did this at one point (could be wrong - I had a lovely Perl script to break street addresses into number, pre-direction, street name, suffix and post-direction that I had to convert multiple times to different things, ending up as an SSIS C# script component).

As 2005 and 2008 are no longer supported, I suspect getting a developer copy to install and do the conversion could be difficult. And, even if you did (as noted in the SO post), the conversion process may not give you what you want.

Back in 2009, I was involved in moving our database (and about 100 DTS packages) from 2000 to 2008. We did use the conversion process, and most of the time it worked fairly well. That said, we also tested the SSIS packages side-by-side against the original DTS packages, to confirm the results. And, now, the conversion process would be a two-step one.

If you've got 10 packages to move, a simple rewrite makes sense (and gives you an opportunity to learn about the additional capabilities that SSIS provides). If you've got dozens of packages, and happen to have access to the 2005 or 2008 SQL Server tools (you don't need to run a server with that version to do the job, I don't believe), then it may make sense to go through the conversion process and see what you get. If most of what you have makes it through fine, that's wonderful - worst case scenario, you're back to a manual rewrite.

0

MY SOLUTION: I found on our old server, we have a tool called SQL Server Enterprise Manager, where under Data Transformation node, I can see all the local packages and design them. Now I'll rewrite them by SSIS on new server, instead of converting them.

  • FYI Enterprise Manager was the predecessor to SQL Server Management Studio, and the tool to create/modify DTS packages (as opposed to a separate utility, like BIDS (Business Intelligence Development Studio) or SSDT (SQL Server Data Tools), both functionally subsets of Visual Studio.. – RDFozz Nov 6 '17 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.