Moving sql 2000 DTS packages to sql 2005 standard edition

I have been tasked with upgrading around 150 SQL Server 2000 DTS packages to SSIS in SQL Server 2005 standard edition. I made a backup of the 2000 database upon which the DTS packages operate and restored it to the SQL 2005 server. So far, so good. I have the database in place. Now I need to get the DTS packages themselves into the SLQ 2005 server. I think I need to check my install and make sure that I have the SQL Server 2000 DTS services installed on the SQL 2005 server. I can do that.

However, I wonder what would be the most effective way to physically get the packages from the SQL 2000 server to the SQL 2005 server. Should I use structured storage files? If so, how do I go about opening them in SQL 2005 in order to save them to SQL server 2005?

I should mention that these packages make heavy use of ActiveX scripting so I am looking at rewriting them from scratch to be SSIS packages. I just need the packages on the SQL Server 2005 box so I can make sure I am creating exactly the same functionality in 2005 as existed in SQL server 2000. Each DTS 2000 individual package tends to be fairly simple and I think I can greatly improve the process by consolidating them.

Thanks in advance for any advice.

S. Wells

You'll need the DTS2000 editor by teh sounds of it. Downloadable from here: http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

-Jamie

|||

Thanks Jamie,

It does appear that this will give me what I need. I had read another, earlier, post on the forum that indicated the legacy components did not have to be separately installed now that 2005 is in production release if one had chosen to install integration services. I did not realize that the designer was not installed as part of the legacy DTS components install. I looked all over the BI interface and could not see where I could open a DTS package and then save it to SQL 2005.

Again, thanks.

S. Wells

|||

Just a quick note to say that there is a new version (an update to the previous link):

Instead of:

Feature Pack for Microsoft SQL Server 2005 - November 2005
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Download from:

Feature Pack for Microsoft SQL Server 2005 - April 2006
http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

6th item down:

Microsoft SQL Server 2000 DTS Designer Components

The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Audience(s): Customer, Developer
X86 Package (SQLServer2005_DTS.msi) - 5088 KB

Also look at

SQL Server 2005 Books Online

SQL Server 2005 Integration Services Backward Compatibility
Updated: 17 July 2006
http://msdn2.microsoft.com/en-us/library/ms143706.aspx

General Compatibility between DTS and SSIS
Because Integration Services is an entirely new product, and not a version upgrade, you will not encounter software conflicts between DTS and SSIS.

You can install both DTS and SSIS on the same server.
You can run both DTS and SSIS packages on the same server when both products are installed. You can run DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS runtime that is installed with Integration Services.
You can save DTS packages in the MSDB database of a SQL Server 2000 or SQL Server 2005 instance. You can save SSIS packages only on a SQL Server 2005 instance.
You can edit DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS Designer that is available for download. You cannot edit DTS packages in Business Intelligence Development Studio. You can edit SSIS packages only in BI Development Studio.