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
Marco Russo
Transform data task (DTS2000) and SSIS
cc96ai
For what it is worth, I think the best migration feature is the inclusion of the DTS 2000 runtime in SQL 2005. < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
I just don’t believe any migration will do a good job, unless it was written specifically for my style of packages, and even then it would be huge due to the variations I have. It just is not worth it. My migration plan will be to upgrade to SQL 2005, and let DTS keep on going. It works, so as far as a migration goes, that is the biggest requirement. As you say, a rewrite will offer much more, but until a rewrite is warranted stick with the tested packages you have today. A migration will probably do a bad job, and then require testing, so save your test resource for the new packages when you have time or need to rewrite properly.
Migration components in the Wizard sound quite nice, but not sure they would work in practice. Starting from scratch would probably not be much harder if you target specific tasks and package styles. If you have used a consistent style, then you could start with a template package anyway.
Alexander Safronov
Easing your smooth transition, you are able to continue to edit the 2000 packages from the new tools. You can also invoke the old package from withing a 2005 package with the Execute DTS 2000 Package Task.
Based on the original description of the packages, I think it quite likely that the DTS Migration Wizard will migrate the packages to a working 2005 package format.
AYK
Totally agreed that DTS Migaration Wizard will not give you any flavour of New SSIS features like
- Logging
- Configuration FIles
- Check Points
- Transactions
If you want to Migrate packages successfully and also want to enjoy features of SSIS then try DTS xChange.
Here is the Product URL
http://pragmaticworks.com/dtsxchange.htm
and here is the full comparison with MS DTS Migration Wizard
http://pragmaticworks.com/Products/compare/DTSxChange-vs-MSWizard.htm
I hope this helps