How to copy DTS 2000 packages between servers (and from SQL 2000 to SQL 2005 and SQL 2008)
I was posed the question today, how do I migrate my SQL Server 2000 DTS packages to a SQL 2005 Server without upgrading them, thus leaving them as legacy DTS 2000 packages within SQL 2005?
In other words, how do I copy or move DTS 2000 packages into SQL Server 2005...
If you did an in-place upgrade from SQL 2000 to SQL 2005 (right on top of the old instance) this isn't a big deal, because the DTS packages are still there. However, if you aren't doing an in-place upgrade, the MSDB database where the DTS packages are stored wasn't kept as part of your upgrade, so maybe you don't have any DTS packages on your new SQL 2005 instance and need to get them over there.
To my suprise, there wasn't much official documentation on the subject, and didn't seem to be any wizard to help accomplish the goal, so its time to get creative...
============
Upgrading packages to SSIS would be best for long term!
The ideal long term solution is to not keep DTS 2000 packages, and instead upgrade your DTS 2000 packages to SSIS 2005 packages either using the Package Migration Wizard (just right click on Data Transformation Services in 2005 or 2008 object explorer), or using the MVP solution DTS XChange from https://www.PragmaticWorks.com (good tool by the way) which has a much better chance of upgrading your DTS package cleanly to SSIS.
If that's not ideal to you... SQL 2005 and SQL 2008 can continue to run DTS 2000 packages in legacy mode (DTSRun.exe to run them, or Execute DTS 2000 Package Task within an SSIS control flow), but keep in mind the next version of SQL will likely not include the ability to run and edit DTS 2000 packages.
In SQL 2005 and 2008 you can even use the DTS 2000 designer within Management Studio to edit such existing DTS 2000 packages, by simply installing the backward compatibility DTS Designer Components (see the latest feature pack download page for "Microsoft SQL Server 2000 DTS Designer Components" https://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en )
But for long term - beware, Microsoft will only support parts of the DTS 2000 legacy features in SQL 2005 and SQL 2008 for the product lifecycle of SQL Server 2000, so if you run into a bug with DTS 2000 itself, could be it is not eligable to be fixed in SQL 2005 and SQL 2008 as part of the DTS 2000 legacy components. There are special considerations and details to read about here
A more complete story of the usage of legacy DTS components in SQL 2005 and 2008 is described in SQL Books Online https://msdn.microsoft.com/en-us/library/bb500440.aspx
============
Let's not worry about upgrading for now... let's just copy what packages I have
So in the short term if you are not prepared to upgrade all your DTS packages to SSIS, the question is how to move your DTS 2000 package and leave them in the old legacy format and continue to run them, in case that you may not have time to immediately upgrade your packages and test them out.
The "Package Migration Wizard" is not the tool for this task, because it converts DTS 2000 packages into SSIS 2005 Packages, thus changing the format to the new format. Migrate in this tool means, really migrate + upgrade.
Never fear, we can think of a few fairly easy solutions on how to move DTS 2000 packages from SQL 2000 (MSDB) to SQL Server 2005 (MSDB) and keep the legacy format:
===========
METHOD 1. You can save each package one by one from 2000 to 2005... maybe a bit tedious, but if you've only got a couple, its simple enough.
+ Open SQL 2000 Enterprise Manager
+ Expand the Server name
+ Expand Data Transformation Services
+ List each package in the right pane
+ Doubleclick each package to open the designer
+ Within the designer, click the Package Menu > save As
METHOD 1A. Save directly to SQL 2005
+ In the Save DTS Package dialogue, select location "SQL Server" and point
to your SQL 2005 Msdb database
METHOD 1B. Save to structured storage files, and copy those files to SQL 2005, and import into Legacy 2000 Packages one at a time.
+ In the Save DTS Package dialogue, save to location "Structured Storage File"
+ Provide a location and name for the .dts file.
+ Copy the file to a location accessible to the SQL 2005 Server
+ Find the Data Transformation Services icon in Object explorer in 2005. (Object Explorer connected to SQL Database Engine, under the Server name > Management > Legacy > Data Transformation Services)
+ Right click > "Import Package File..." and point to the structured storage file.
(NOTE: I got a few errors when the DTS 2000 package had multiple versions stored inside when using this method B. I didn't have time to investigate, but its worth mentioning there could be some complications with this method.)
===========
METHOD 2. Copy the rows in the sysdtspackages table from the old msdb to the 2005 msdb. Seems easy enough... and this is better when you have a lot of packages to move.
Similar to: https://www.sqldts.com/204.aspx
The packages live mainly in MSDB database in the table "sysdtspackages". The actual contents of the DTS package live in the column "packagedata", which is an Image column. Its hard to "see" the contents in the table, but you can see the names of the packages and dates and such.
I tried to use the Import Export Wizard to achieve this method, but since the wizard doesn't list System tables in MSDB as being eligable for transfer, that's not a viable option.
Therefore I see two easy methods
A. Restore a renamed copy of MSDB to the new server and copy the rows from the old table to the new table.
B. Use a linked server from 2005 pointing back to 2000 to copy the msdb sysdtspackages table rows.
===========
METHOD 2A: Use backup/restore to get the msdb table over to the 2005/2008 server
Warning: I'm not suggesting you restore a SQL 2000 MSDB database over top of (replacing) a SQL 2005 or SQL 2008 MSDB database, since that will cause a major problem. System databases cannot be copied between SQL versions like that, and SQL Agent will not start if you attempt that. Instead, the suggestion for this method is that you restore the SQL 2000 MSDB database with a NEW NAME and it will become a user datbase (not a system database) in SQL 2005/SQL 2008.
-- on SQL 2000 machine
backup database msdb to disk='c:sql2000msdb.bak'
-- Copy the backup file to a folder where SQL 2005/2008 can get to it (UNC share or local disk, but local disk is less error prone)
-- on the destination SQL 2005/2008 machine, restore the 2000 msdb with a new name and rename the .mdf and .ldf files too
RESTORE DATABASE my2000msdb
FROM DISK='c:sql2000msdb.bak'
WITH MOVE 'msdbdata' TO 'c:tempmsdb_2000.mdf',
MOVE 'msdblog' TO 'c:tempmsdb_2000.ldf'
-- copy rows from the restored 2000 table into the 2005 msdb. Run on the destination 2005 machine:
INSERT INTO msdb.dbo.sysdtspackages
SELECT * FROM my2000msdb.dbo.sysdtspackages
-- check the results
SELECT * FROM msdb.dbo.sysdtspackages
===========
METHOD 2B: Linked servers make life easy - no file copy needed.
Make a linked server (pointing back from 2005/2008 to the 2000 servername)
+ Connect to Object Explorer in 2005/2008 Management Studio.
+ Expand the SQL 2005/2008 Server name
+ Expand the Server Objects folder
+ Expand the Linked Servers folder
+ If you don't already have a linked server pointing to your SQL 2000 server, right click and make a new one (Linked Server name is your SQL 2000 server name (serverinstance) and the server type is "SQL Server" with security "Be made using the login's current security context")
-- Use 4-part naming in your FROM clause to copy the rows from the 2000 server msdb into the 2005 msdb table. Note the square brackets for my sql 2000 server name, which are helpful if you have a space in your server name, or a backslash for the instance name such as [servernameinstancename]. Add a where clause if you only want certain packages moved.
INSERT INTO msdb.dbo.sysdtspackages
SELECT * FROM [linkedserver2000].msdb.dbo.sysdtspackages
INSERT INTO msdb.dbo.sysdtspackages
SELECT * FROM [linkedserver2000].msdb.dbo.sysdtspackages WHERE name LIKE '%packagename%'
+ Consider dropping the Linked Server (right click - delete) after your DTS package copy (if you aren't using it for other purposes) since that can leave a security opening if you forget to lock it down, but don't do that if other apps depend on it!
===========
METHOD 3. Use DTS package API's to do the DTS package move:
There is a tool called "DTS Backup 2000" from a 3rd party website which utilizes a bit of magic behind the scenes to copy some or all of your DTS Packages. Read about it:https://www.sqldts.com/242.aspx Download it: https://www.sqldts.com/272.aspx
Seems simple enough, and though I don't endorse it on behalf of Microsoft, but on the surface seems very nice and simple.
+ Point it to your source SQL 2000 server.
+ Pick the packages you want to copy. Provide a password if needed.
+ Point it to your destination SQL 2005 server.
+ Voila you are done... just need to test.
===========
After Thoughts...
Now that you got them copied into SQL 2005/2008 MSDB, you should check them all and make sure they got copied successfully and that they work.
DTS 2000 legacy packages appear in the list in SQL 2005/2008 Management Studio Object Explorer under the Server name > Management > Legacy > Data Transformation Services.
Packages copied in the above manners will NOT show up in Object Explorer connections to the SSIS (Integration Services) , because they have not been migrated to SSIS.
SSIS packages live in either the file system, or in a different table. The Integration Services service and runtimes (DTExec.exe, DTExecUi.exe) allows you to browse only these kinds of locations:
- XML documents (*.dtsx files) on your hard drive,
- in the sysdtspackages90 table in MSDB (select * from msdb.dbo.sysdtspackages90) for SQL 2005
- in the sysssispackages table in MSDB (select * from msdb.dbo.sysssispackages) for SQL 2008
- SSIS Service pointers to files and MSDB from the (MsDtsSrvr.ini.xml) file to the above locations
DTS legacy packages live in the msdb.dbo.sysdtspackages (minus the 90 ending) table and are displayed in SQL Server object explorer under the Management > Legacy components in object explorer.
===========
After copying them, make sure to test them.... and think about server names, OLEDB providers and ODBC drivers needing to be installed on this new machine, and test connectivity. If you DTS Package needs to access any flat files on shares, make sure those shares are set up and working for the security context which will be running the package (SQL Agent or a SQL Agent Proxy Account)
Consider that after moving from one old 2000 server to a new 2005 server, the Connection String references within the DTS 2000 package have not changed. If they point to the SQL 2000 database server, that didn't change, and will not point to your new SQL 2005 server, so you made need to open each DTS 2000 package and edit those connection strings and server names to point to your new server if your design requires.
Alternatively you can build an "Alias" via the "SQL Server Configuration Manager" in the start menu to redirect ALL requests to a certain server name (old server name) to a different location (the new server). Beware that all client apps on the machine where you set this (presumably the server in this scenario where you run your DTS 2000 packages in SQL 2005) so this may be undesirable since it affects much more than just DTS 2000 legacy packages, but if you can't open each and every package to do the edit, then it may be the easiest route.
Consider, since DTS packages can only run in 32-bit mode (there is no 64-bit DTSRun.exe) any connectivity libraries settings and providers and any Aliases installed would be the 32-bit versions.
If your DTS packages point to 3rd party databases, using ODBC drivers or OLEDB providers, those providers and drivers have to be installed on whatever machine the DTS packages will run from. You might have to take inventory, and make sure that connectivity works before setting up any SQL Agent jobs to run these newly moved packages on the destination machine. You can test ODBC from the DSN ODBC Datasources (in administrative tools) and to test OLEDB, I like to rename a notepad .txt file to extension .udl and doubleclick the .udl to step through the Universal Data Link wizard to point to an OLEDB provider and make sure it at least establishes a test connection successfully before worrying about DTS package internals.
FYI, I won't get into the details today, but I imagine you can script any SQL Agent jobs from your SQL 2000 machine (right click the job in Enterprise Manager > All Tasks >Generate SQL Script...) and run those scripts on SQL 2005 msdb to get a similar job set up for scheduling your legacy DTS packages.
If you find your DTS Package fails after being moved, the best way to get a good detailed error report is to open the DTS Designer, click the background of the design surface, Select the "Package" menu > Properties. Fill in the box with an "Error file:" name and location. Run the DTS package and the output log will be informative (hopefully) on what caused the failure.
===========
In summary, I hope this helps you save your DTS 2000 packages in a way that's painless until you can spend more time and upgrade the packages to the newer SSIS format.
Best wishes with DTS and SSIS, and happy upgrades! - Jason
=====================
| Jason Howell | SQL Server Escalation Support | Charlotte, NC |
=====================