Even though you can indeed run DTS packages from within SSIS the solution is only partially complete. In Management Studio, you can manage your DTS packages under Managment --> Legacy --> Data Transformation Services. From here, you can open packages, import new ones but you cannot create a new DTS package. To create a new one, you can open an old one, delete everything in the package and then do Package --> Save As, naming it the new name. That's a not so elegant workaround but it will work!

When you upgrade to SSIS, you can see a dramatic improvement in performance. For example, I took a simple DTS package which pulls one million rows out of a comma-delimited text file and writes that data to a SQL Server table. In DTS the average runtime over five runs of the package was 33.2 seconds for the million records. The same upgraded package took 11.3 seconds on average if you choose to migrate to a SQL Server Destination in the Data Flow task or 12.3 seconds on average for a standard OLE DB Destination. This was on a multi-core laptop with 2 GB of RAM and the package was run 5 times for closer accuracy.

Keep in mind that this package was quite simple. If you migrated to an SSIS package with a more complex transformation, the results would be much, much more impressive. But, with a simple package with no transformation, there was close to a 65% performance improvement between DTS and SSIS. Notice also that the SQL Server Destination runs slightly faster also than the OLE DB Destination. The reason for that is the SQL Server Destination writes directly into SQL Server's shared memory space and skips the TCP/IP stack. The package here gives about an 8% gain in performance but this could be less as the SSIS package had more transformation and the share of the destination load became less of the work.

Another key point about running DTS in the 2005 or 2008 runtime. There is no way to look at the DTS package logs as you did in Enterprise Manager. To do this, the workaround is to create reports or queries to go against the sysdtslogs tables in the msdb database. A far cry though from the old Enterprise Manager interface. The moral of the story in my eyes is to not be lured into running DTS in the 2005 or 2008 environment. While it will work, it does create a management headache. Another key is that the DTS runtime will not run in the 64 bit environment.

If you need help migrating DTS packages to SSIS, check out http://www.DTSxChange.com.

-- Brian Knight