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)