I've been working with SQL Server 2000 DTS (Data Transfer Services) for some time.  DTS allows you to drop and create tables as well as import or export complete data sets between different database instances.  For example, I work with a local copy of the database and want to deploy changes to a staging server database during the development process.

Recent experience with SQL Server 2005 has left me disappointed in Microsoft yet again.   My development environment is SQL Server 2000 running on VMWare Fusion on a Mac.  First, I discovered that you could not use SQL Server 2000 DTS tools to update database schema/data in a SQL Server 2005 database.  So ... I purchase the developer edition of SQL Server 2005, which includes SQL Server Management Studio.  I thought this would be the end of my difficulties, but I was wrong.  DTS in SQL Server 2005 does not create primary keys, indexes or default values.  A simple example would be an "id" field that auto increments.

As you can see, the data is corrupted. 

Others have blogged about this issue and indicate that Microsoft isn't moving to solve these problem anytime soon.  So, I've documented my work around and created a pdf for others to use.

Here are the basic steps.

1. Generate SQL Scripts to create your tables
2. Use a third party utility called Simple SQL Bulk Copy.  This will preserve your primary key information during the export process.