There are two options to move from 2000 sql server to 2005 sql server
database engine.
1. Double click on the DTS package to open in the designer.
Click file--> save as and change the destination server to SQL server
2005. Then save.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
2. Save the DTS package as a structured storage file and import
it in sql server 2005 database engine under management --> legacy -->
Data transformation services.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
What is the recommended method out of these two? Please illustrate.My recommendation would be the second. I would also be careful in testing
what the Migration Wizard does when moving it to an SSIS package. If your DTS
package contains a lot of script components, "odd" things can happen. Since
you can still run and maintain your DTS packages under the Migration section
(just not create new ones), depending on the volume of use of DTS, I often
recommend to not migrate and slowly create new ones in SSIS. SSIS is a
daunting tool compared to the DTS designer, so even maintaining once
converted could be time consuming.
My 2 cents - hope that helps.
"sudesh.withanage@.gmail.com" wrote:
> There are two options to move from 2000 sql server to 2005 sql server
> database engine.
> 1. Double click on the DTS package to open in the designer.
> Click file--> save as and change the destination server to SQL server
> 2005. Then save.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> 2. Save the DTS package as a structured storage file and import
> it in sql server 2005 database engine under management --> legacy -->
> Data transformation services.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> What is the recommended method out of these two? Please illustrate.
>|||I agree with James as in my experience the conversion in most cases seems to
be unsuccessful. If you have UDL connections or you use a lot of scripting,
especially scripting of the DTS object model, then all that you can convert
to is a placeholder to execute the DTS 2000 package, so you'll be in pretty
much the same situation either way.
I'd recommend refactoring the DTS packages one by one. We're doing this to
take advantage of the new functionality. There's not much on migration
strategies in BOL, but my 2Ps worth is to spend time creating a generic
template that can be used for all your DTS packages. This can include
centralised event handling and centralised configurations. If you get this
right, the package will work in Dev, Test and Live without any
reconfiguration at all, and you'll be able to monitor all packages
centrally.
Cheers,
Paul Ibison
