Running DTS Packages under SQL Server 2005 and 2008
Just because you are running SQL Server 2005 and 2008 does not mean you can no longer execute DTS Packages that were created under SQL Server 2000. There is no need to rewrite your DTS Package to run under SQL Server Integration Services (SSIS).
Here is how you can install and run DTS Packages on SQL Server 2005 and 2008:
- Before you begin, you should have already installed Integration Services on your SQL Server 2005\2008 server.
- Your SSMS client must have "Microsoft SQL Server 2000 DTS Designer Components" installed.
- DTS Packages can be managed from the SQL Server Management Studio (SSMS) and can be found under \Management\Legacy\Data Transformation Services\ tree.
- Let's assume you have a DTS package file used on a SQL Server 2000 machine which now needs to run an a new server running SQL Server 2005\2008 which contains the target database(s) which the DTS package operates on.
- To add a DTS package to SQL Server 2005\2008, right-click on Data Transformation Services and select Open Package file.
- Select the DTS package file (*.dts)
- You should now see the DTS package as you would normally have seen it with Enterprise Manager.
NOTE: The DTS 2000 Package Designer window does not allow you to switch to/from other windows in the SSMS client. You need to first close DTS 2000 Package Designer window.
- Make the necessary changes to the package by adjusting the server name, database, user name, password, etc. This may be via Global Variables, Dynamic Properties, Disconnected Edit or even ActiveX script and depends entirely on the DTS package.
- Save the DTS package to the 2005\2008 SQL server making sure that the Location is "SQL Server" and you have specified the correct server name and credentials.
- Refresh the Data Transformation Services SSMS tree to see the new DTS package.
- To edit the DTS package again, right-click on it and select Open (avoid Migrate or Export as there is much "badness" there!)
- After the second time you saved the DTS package, you will be presented with the following dialog box asking you to select the version you want. The more edits you do the more versions you will see in the list. Usually, the latest version appears at the top of the list. Be sure to double check though!
- To schedule your DTS package, go to the \SQL Server Agent\Jobs\ tree in SSMS and create a new Job.
- Create a new step which runs the following Operating System (CmdExec) type:
DTSRun /S servername /E /N "dts package name"
- Naturally, the Job should run under an appropriate credential which depends entirely upon your environment.
- Run you job and check the results. There you have it.
To recap, there is no need to rewrite your old DTS packages into SSIS packages. Keep clear of the option in SSMS called "Migrate DTS 2000 Package" as this does not work correctly. Avoid migrating or exporting DTS packages as they do not entirely work.
If you've had experience writing DTS packages, the SSIS method is a completely different ball game. Although SSIS is much more powerful, it does not provide DBAs with an easy and simple way to import/export/transform data. It is a full blown programming environment which does not allow you to quickly do things like data transformations without heavy coding. Let's hope that Microsoft still supports DTS Packages in future releases or provides a similar ETL tool.