THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Transform Data Task (DTS2000) and SSIS

My first period using SSIS in a real-world application convinced me that there is a lack of support in migration of SQL2000 DTS packages, expecially in a star schema transformation scenario.

Most of my actual DTS packages are combinations of SQL Execute Task and Transform Data Task.
90% of Transform Data Task are so composed:
- SELECT from Data Source
- mapping from source to destination with "copy column" (source and destination columns have the same name)
- SQL Fast Load with a defined batch size (1000 or 2000 tipically) and Table Lock
- Log to text file of source and destination rows that fail transformation (tipically when I try to put NULL in a NOT NULLable column)

In a SSIS package I could have a data flow task corresponding to the Transform Data Task. It would be very easy to handle my tipical use of Transform Data Task. While I understand that there are many other uses that could be not so easy to translate, a wizard that try to convert a transform data task into a data flow before to convert it into a Execute legacy Package task would be very useful.
I completely understand that a rewrite of my DTS could give me great advantages, but from a practical point of view I could facilitate the adoption of SQL 2005 if DTS packages would run AND would be editable in the new native environment, allowing a progressive optimization and a gradual adoption of the new features.

If this scenario is not to be supported, it would be good at least to have a chance to integrate an external "migration component" into the Migration Wizard. Is there a way to do that? Or if I want to support a similar scenario I have to rewrite the whole migration wizard?

(this post is cross-posted into the yukon.dts newsgroup)

Published Sunday, June 19, 2005 4:18 PM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement