Is it possible to make DTS Packages that can easily target different
destination SQL Server servers and databases?

I'd like to have the package modified as little as possible as I proceed
through the development lifecycle. Starting with my development
environment, then to a QA database server with a different DB name (same
schema), then finally to the production deployment.

Are global variables the way to do this? If so, then I'm afraid that I
don't understand how to use them on the connection object and in the
transforms.

- Rick

In article <jumdnUUWJIN-66-iU-KYgw (AT) comcast (DOT) com>, RickT
<thumper (AT) kqrsrocksNOSPAM (DOT) com> writes
Have a look at the Dynamic Properties Task. This can read global
variables and assign the value to virtually any task, step or connection
property in that package.

Look up "Dynamic Properties task" in SQL Server Books Online for some
more information.

Using parameters in this way for any property that may change is good
practice in my opinion.

For connections you can also abstract the server name by using an alias
created via the Client Network Utility. This is for the server name only
unfortunately.

You could also change your connections to "Microsoft Data Link" which
uses UDL files. These are really just text files with a connection
string in. If you have a common file path and use functional names for
the UDLs you should never need to change the UDL file property in you
connection during migration, as each server (environment) has it's own
copy of the file.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Remove the DB component, it is not required, and since it overrides the
connection catalog, it is just a pain in most cases. One it ios removed just
set the connection to the correct DB and it should be happy.


--
Darren Green
http://www.sqldts.com

"RickT" <thumper (AT) kqrsrocksNOSPAM (DOT) com> wrote