Sql-server – how to edit and migrate DTS package on SQL server 2000

dtssql-server-2000

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.

Best Answer

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.

Related Question