We can either run the DTS packages Execute DTS 2000 Package Task within an SSIS or we can completely upgrade them to new SSIS solution.

Definitely Upgrading packages to SSIS would be best for long term solution in today's world!

Best and the  long term solution would be not keep DTS 2000 packages, and instead upgrade your DTS 2000 packages to SSIS 2005/2008 packages by using the Package Migration Wizard (just right click on Data Transformation Services in 2005 or 2008 object explorer).

You can start the Package Migration Wizard from three locations.

• From SQL Server Management Studio. Connect to an instance of the SQL Server Database Engine, right-click the Data Transformation Services node in Object Explorer under Management\Legacy, and select Migration Wizard.

• From Business Intelligence Development Studio. Create or open an Integration Services Project, right-click on the SSIS Packages node in Solution Explorer, and select Migrate DTS 2000 Package.

• From the command prompt. Start DTSMigrationWizard.exe from the C:\Program Files\Microsoft SQL Server\100\DTS\Binn folder.

If upgrading to SSIS in SQL 2005 and SQL 2008  is not the best solution, then we 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 we should  keep in mind the next version of SQL will likely not include the ability to run and edit DTS 2000 packages.

One thing about SQL 2005 and 2008 is that we can even use the DTS 2000 designer within Management Studio to edit such existing DTS 2000 packages. And it can be achieved by simply installing the backward compatibility DTS Designer Components (see the latest feature pack download page for "Microsoft SQL Server 2000 DTS Designer Components http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en)

Find the Microsoft SQL Server 2005 Backward Compatibility Components in the below link:

http://www.microsoft.com/downloads/en/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en

How to Copy DTS packages and run them as it is?

If instant upgrade for all your DTS packages to SSIS is not a feasible solution ,then  you might want to know how to move 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. "Package Migration Wizard" is not the best tool for this task as it converts DTS 2000 packages into SSIS 2005/2008 Packages changing the format to the new format. Migrattion with this tool means migrate + upgrade to new SSIS format.

However, we can think of some easy solutions on how to move DTS 2000 packages from SQL 2000 to SQL Server 2005 and keep the legacy format:

Method I> You can save each package one by one from 2000 to 2005. It may be a bit tedious method, but if you've only got a couple, its simple enough.

1.  Open SQL 2000 Enterprise Manager
2. Expand the Server name
3. Expand Data Transformation Services
4. List each package in the right pane
5. Doubleclick each package to open the designer
6. Within the designer, click the Package Menu > save As

We can either Save directly to SQL 2005

1.  In the Save DTS Package dialogue, select location "SQL Server" and point to your SQL 2005 Msdb database 

OR we can save to structured storage files, and copy those files to SQL 2005, and import into Legacy 2000 Packages one at a time.

1. In the Save DTS Package dialogue, save to location "Structured Storage File"
2. Provide a location and name for the .dts file.
3. Copy the file to a location accessible to the SQL 2005 Server
4. 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)
5. Right click > "Import Package File..." and point to the structured storage file.

METHOD II> Copy the rows in the sysdtspackages table from the old msdb to the 2005 msdb.

This is better solution when you have a lot of packages to move. 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. It is hard to see the contents in the table, but you can see the names of the packages and dates and such. Import Export Wizard doesn't list System tables in MSDB as being eligible for transfer. So that won't be a viable option.

Two easy methods are:
II1. Restore a renamed copy of MSDB to the new server and copy the rows from the old table to the new table.
II2. Use a linked server from 2005 pointing back to 2000 to copy the msdb sysdtspackages table rows.

METHOD IIA: Use backup/restore to get the msdb table over to the 2005/2008 server

1. on SQL 2000 machine backup database msdb to disk='c:\sql2000msdb.bak'
2. 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)
3. on the destination SQL 2005/2008 machine, restore the 2000 msdb with a new name and rename the .mdf and .ldf files too
4. RESTORE DATABASE my2000msdb FROM DISK='c:\sql2000msdb.bak' 
   • with MOVE 'msdbdata' TO 'c:\temp\msdb_2000.mdf', 
   • MOVE 'msdblog' TO 'c:\temp\msdb_2000.ldf'
5. 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
6. check the results SELECT * FROM msdb.dbo.sysdtspackages

METHOD IIB: Linked servers make life easy - no file copy needed.

 Make a linked server (pointing back from 2005/2008 to the 2000 servername)

1.  Connect to Object Explorer in 2005/2008 Management Studio. 
2.  Expand the SQL 2005/2008 Server name
3.  Expand the Server Objects folder
4.  Expand the Linked Servers folder
5.  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 (server\instance) and the server type is "SQL Server" with security "Be made using the login's current security context")
6. 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 [servername\instancename]. 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%'
7. 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!

We can run a DTS package that is saved by DTS tools as a Microsoft Visual Basic (.bas) file. A Visual Basic .bas file consists of declarations and a Sub Main and may contain other Subs called by Sub Main with all logic of the DTS package.

Steps for using a Visual Basic module (.bas) file into a Visual Basic project and executing it on a computer running the Microsoft SQL Server™ client tools:

1. Create a new Standard EXE project.
   
2. On the Project menu, select References, and then add references to  Microsoft DTSDataPumpScripting Object Library, Microsoft DTSPackage Object Library, andMicrosoft DTS Custom Tasks Object Library check boxes.
3. On the Project menu, click Add File, and then add the Visual Basic file produced by the DTS Import/Export Wizard or DTS Designer.
   
4. In the Project Explorer, Remove Form1 from the  Project menu
   
5. Run the project. 

   There will be No indication of completion but Visual Basic Development Environment will go back to design mode. 

The Visual Basic project generated Visual Basic file from above can be used to save the DTS package to SQL Server.

Steps for saving Visual Basic files to SQL Server:

1. Go to the end of the Sub Main of Visual Basis File and uncomment the line

   'goPackage.SaveToSQLServer "(local)", "sa", ""

2. Comment out the following line

   objPackage.Execute
   

3. Run the project.

   As the Visual Basic Development Environment goes back to design mode, the package is saved to SQL Server.

The package can now be run from DTS Designer and can be saved again as a Visual Basic file from DTS Designer.