Have DTS packages in SQL2000 and now your are upgrading to SQL2005?
I know, I know... the "correct" answer (according to Microsoft) is to migrate all DTS packages into SQL Server Integration Services packages, but... The migration tool is less than stellar, and the Microsoft Visual Studio tool is much more complex and complicated than the simple flowchart-style DTS package editor window. Unless you have a developer on hand to dedicate to the manual migration and translation of DTS packages into SSIS packages, perhaps you may want to take another route.
First, you will want to install the Microsoft SQL Server 2000 DTS Designer Component for SQL Server 2005, which can be found here: DOWNLOAD.
Next, export your DTS packages. This will create neat, simple files on the OS that you can cut and paste wherever you like.
Then, in the Microsoft SQL Server Management Studio go to the server you want to put the package on, navigate to Management -> Legacy -> Data Transformation Services, right click in the results window and select Import Package File (which you just exported in the previous step).
Finally, right click on the imported package and choose the "open" option to edit the package in the oh-so-familiar DTS Editor window.
Now you have your DTS packages on your SQL Server 2005 instance, and a warm fuzzy for knowing you haven't lost the years of blood, sweat and tears that went into creating them.
Now, if you would like to schedule these packages to run as a SQL Job, it is pretty simple.
Just create a SQL Job that calls an external command, and have it execute DTSRun.exe. If you go to a command prompt and type in DTSRun.exe -help it will give you a list of the parameters you can feed it, like what server to run it on, what user context to use and which package to execute.
Hope this helps...
