There is a lot to be said for building DTS packages on a good
foundation.

For setting up your packages to perform changes dynamically, check out
this best practices document from Microsoft,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k
/html/sql_busintbpwithdts.asp

For the majority of our packages, the global variables are stored in a
database table that only the DBA and the Server can access. The packages
have half a dozen static global variables defined that provide the
connection properties to this database table.

To execute the package we use DTSRUN like this
DTSRun /S "<<SERVER>>" /N "My DTS Package" /A
"GV_SERVER":"8"="<<SERVER>>" /A "GV_DATABASE":"8"="<<DB>>" /A
"GV_TRUSTED":"8"="True" /W "0" /E

At run-time, the package assigns the passed parameters to the database
connection, retrieves it's list of global variables and generates them
dynamically.

With this setup, changing servers, or destinations for extract files,
becomes a simple table editing procedure instead of having to fiddle
with DTS.



Thanks
Phillip Carter
Ph: +61 3 9235 1691


-----Original Message-----
From: Rogers Michael L
[mailto:mssqldba-ezmlmshield-x23460259.[Email address protected]
Sent: Thursday, 30 June 2005 2:19 AM
To: LazyDBA Discussion
Subject: Changing a connection on DTS

Why is it that every time I want to make a simpe connection change in a
DTS package that I essentially have to re-write everything? Am I the
only one that has problems with this? I change the connection and have
to re-do the transformations, then, when I run it, it still point to the
old tables/connection even though I have changed everything.

Frustrated again



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html


IMPORTANT DISCLAIMER - THIS MAY AFFECT YOUR LEGAL RIGHTS:

Because this document has been prepared without consideration of any
specific clients investment objectives, financial situation or needs,
a Bell Potter Securities Limited investment adviser should be
consulted before any investment decision is made.

While this document is based on the information from sources which
are considered reliable, Bell Potter Securities Limited, its directors,
employees and consultants do not represent, warrant or guarantee,
expressly or impliedly, that the information contained in this document
is complete or accurate.

Nor does Bell Potter Securities Limited accept any responsibility to
inform you of any matter that subsequently comes to its notice, which
may affect any of the information contained in this document.

This document is a private communication to clients and is not intended
for public circulation or for the use of any third party, without the
prior approval of Bell Potter Securities.


Disclosure of Interest: Bell Potter Securities Limited receives commission
from dealing in securities and its authorised representatives, or introducers
of business, may directly share in this commission. Bell Potter Securities
and its associates may hold shares in the companies recommended.

Bell Potter Securities Limited ABN 25 006 390 772 AFS Licence No. 243480

MS Sql Server LazyDBA home page