Upgrading Data Transformation Services (DTS)

Upgrading SQL Server Business Intelligence

Andrew Fryer's Blog — Fri, 28 Mar 2008 19:38:41 GMT

Continuing my series of posts on upgrade, I thought it might be good to have a look at the business intelligence

re: Upgrading Data Transformation Services (DTS)

Andrew.Fryer — Tue, 25 Mar 2008 09:18:44 GMT

Brian

I Totally agree - I wasn't suggesting you keep your packages in DTS if you have less than 20 of them, just that it may be cheaper for them to convert them manually rather than invest in DTS-XChange.

re: Upgrading Data Transformation Services (DTS)

Brian Knight — Wed, 19 Mar 2008 15:31:38 GMT

Hi Andrew, thanks for this great post.

I agree with most your items but I'm afraid I must disagree with your last sentence though about keeping DTS when you have less than 20 packages. From our customer experience, customers that do this are extremely dissatisified. A few reasons: lack of true 64 bit support for the DTS runtime (dtsrun.exe), lack of a method to easily create new packages and lack of logging.

The lack of a method to create new DTS packages can be worked around by modifying an existing package and doing a Save As but it is very awkward. On the logging side, in each package, you can create a Package Log as you did before but the UI to see those logs is gone. You would have to create your own reporting to view Package Logs in DTS under SQL Server 2005. This creates a huge manageability issue for many customers.

I wrote up a quick performance study about SSIS vs DTS here as well and it is easily 65% better in performance without doing at all other than upgrading. This to me shows that Microsoft got tons right in the SSIS engine but DTS support isn't one of them.

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/03/12/ssis-lab-runtime-performance-differences-between-dts-and-ssis.aspx

re: Upgrading Data Transformation Services (DTS)

Jonathan Noble — Thu, 13 Mar 2008 17:02:12 GMT

Thanks for that Andrew - that advice will certainly ease our migration. There had been some worries about the amount of DTS that needed re-written.