all groups > sql server dts > july 2007 >
You're in the

sql server dts

group:

how to migrate all DTS packages from SQL2000 to SQL2005 in one go


how to migrate all DTS packages from SQL2000 to SQL2005 in one go Tiffany
7/11/2007 2:38:03 AM
sql server dts: Hi

Need to migrate all DTS packages from SQL2000 to SQL2005. May i know how to
do it effectively. Also, to ensure all the transformations in the DTS are
intact.

Thank you

Best regards
RE: how to migrate all DTS packages from SQL2000 to SQL2005 in one go Paul Ibison
7/11/2007 4:10:02 AM
You can start the Package Migration wizard from the Management, Legacy, Data
Transformation Services folder by right-clicking. The wizard will allow you
to select multiple packages in one go. I'd recommend reading up on the
upgrade process in as many places as possible - there's plenty of info on the
web for this eg
http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/. In my
experience a package with any level of complexity won't migrate
correctly/completely. UDL files and ActiveX script transformations are just 2
cases which caused major problems for us. Thinking more widely about this
process, I found that a normal migration is the least attractive option and
in the end preferred to create the SSIS packages from scratch. This way I
take advantage of the new tasks and components, logging, indirect
configuration etc which makes the process worthwhile - just my 2ps worth.
HTH,
Paul Ibison
RE: how to migrate all DTS packages from SQL2000 to SQL2005 in one go Allan Mitchell
7/11/2007 4:22:13 PM
Hello ,


Just to add to Paul's reply. It is also my expereince that a migration is
difficult for the wizard if there is any level of complexity but also even
if the package IS migrated successfully the wizard really has no way of doing
things in the best way. Take Looping for instance. In DTS we did this through
code but in SSIS we do this using one of the lopping constructs we have now
in the control flow. there is no real way the wizard can convert your code
into the SSIS equivalent.

I tend to leave 2K packages as they are and they will execute happily in
2K5. I then hand-migrate in slow time. This way I get an optimal package.



--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

[quoted text, click to view]

AddThis Social Bookmark Button