One of the challenges with upgrading SQL Server from SQL Server 2000 or SQL Server 2005 to a more modern version is that DTS (Data Transformation Services) is no longer supported. It was support for backwards compatibility in SQL Server 2005 and SQL Server 2008/2008 R2. Beginning with SQL Server 2012, DTS packages are no longer supported at all and must be converted to SSIS (SQL Server Integration Services) packages.

This how-to covers the practical process for upgrading old DTS packages to new, shiny SSIS packages.

Step 2: If DTS Packages are in SQL Server 2000, set up a new SQL Server 2008 R2 server

You cannot go directly from SQL Server 2000 to SQL Server 2012 or newer. You have to go to an interim version first. This is true for databases as well as DTS Packages. Using SQL Server 2008 R2 as the interim step (the highest version you can use in this scenario) as it gives you the greatest possibilities for upgrade paths in the next phase of the upgrade.

NOTE: DTS is a 32 bit only process so install 32 bit version of SQL Server 2008 R2.

NOTE: Be sure ot include the client components in the SQL Server installation as you will need to use BI Development Studio (BIDS) later.

Step 3: If DTS is in SQL Server 2005, leave in SQL Server 2005

You can upgrade directly to SQL Server 2012 or SQL Server 2014 from SQL Server 2005. If you want to move to SQL Server 2016, then upgrade DTS in SQL Server 2005 and then migrate to SQL 2012 or 2014 as an interim upgrade step to SQL Server 2016.

Step 4: Install DTS run-time and designer support on the new SQL Server 2008 R2 instance

Step 5: If using SQL Server 2008 R2 as an interim step, export all DTS packages to files and import into SQL Server 2008 R2 (optional)

Packages can be left stored in the file system instead of importing directly into SQL Server 2008 R2.

Step 6: Make any changes to the DTS packages that you want to make prior to migration

If Upgrade Advisor reported any potential conflicts (such as illegal characters in the file name) that you can address in the DTS package itself, open the DTS package in DTS Designer and make the changes

Method 2: 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.

Step 8: Select the package source

Can be packages stored in msdb or the file system. Migration wizard does not support packages stored as Visual Basic files or Metadata storage.

Step 9: Select the package destination

The destination can be the msdb database in SQL Server 2008 R2 or in the file system. If using the file system, the new packages will have the file extension .dtsx which is the file extension for SSIS packages.

Step 10: Select packages to be upgraded

Step 12: Specify a log file to log migration errors

Set path and name of the log file you would like to use. Use a different log file for each set of migrations to avoid confusion if doing more than a single migration.

Step 13: Watch the migration process

If the migration encounters any failures, it will prompt you to choose to end the migration or skip the problem file. Choose skip for any failures.

Step 14: Open successfully migrated packages in BIDS and validate

Successfully migrated packages should be opened in Business Intelligence Development Studio (BIDS - installed with the SQL Server 2008 R2 client tools) and validate conversion complete. Make any changes needed.

Step 15: Review the migration log for any failed packages

If possible, make changes to the DTS packages to correct the issues identified in the migration log and re-migrate the fixed packages.

Step 16: Manually migrate any remaining DTS packages

If there were any packages that you couldn't migrate at all, you will have to recreate the package functionality using BIDS.

In my experience, about 60% of packages are able to migrate to SSIS cleanly with no changes (simple). 30% will require some post-migration modifications (medium). 10% will require manual migration (complex).