September 24, 2002 at 7:44 am
Hi all,
I'm curious to know how people move DTS Packages between Servers. For example moving between Dev, QA & Prod. Do you simply Save the DTS to another package then go through and rename the Connections?
I started using Dynamic Properties for this kind of thing and it works well for me (seperate INI files on each server with the connection parameters).
Just interested in other DBA's idea's & solutions!
Thanks,
Clive Strong
September 25, 2002 at 3:57 am
Most of what I do is transferable by defining connection as local for SQL connections to local box and just save to other servers. In some cases I do have to make connection changes for remote servers.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 26, 2002 at 3:23 am
Check out "Disconnected Edit" in Enterprise Manager. I haven't used it, but many properties are exposed.
Don
Don
http://www.biadvantage.com
September 26, 2002 at 6:02 am
You could also use Dynamic properties to read from a table in the database.
Another way is to use Server 'Aliases' - write your package to use the Alias name instead of the real server name e.g. 'Sales', and then have the 'Sales' alias on Dev, QA and Prod, but pointing to the real server.
Or you could do similar with a UDL. Use the same UDL file name to get your connection information, but vary the actual contents of the UDL file on each server.
Check out sqldts.com for examples
September 26, 2002 at 7:34 am
If you used database alias then you do not need to modify the connection string.
Amir
quote:
Most of what I do is transferable by defining connection as local for SQL connections to local box and just save to other servers. In some cases I do have to make connection changes for remote servers."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 26, 2002 at 7:48 am
Here is how-to define database alias:
Step 1. Create and OLEDB connection string with value of connection string, I.e.
DataSource, database, SSPI, server/instance name, and other connection attributes that you want to be default.
Step 2. Add this information in Windows registery:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLserver\Client\ConnectTo]
"dbalias"="DBMSSOCN,\\\\servername\\instancename,port id"
step 3. Use above name in DTS database server name.
I have written a word doc that explain all these . Send me an email to mousavizadegan_ab@naptheon.com and I would be happy to send you the doc.
PS. for accessing oracle data you would define an entry in TNSNAMES.ora file for database alias
Amir
[
quote:
Hi all,I'm curious to know how people move DTS Packages between Servers. For example moving between Dev, QA & Prod. Do you simply Save the DTS to another package then go through and rename the Connections?
I started using Dynamic Properties for this kind of thing and it works well for me (seperate INI files on each server with the connection parameters).
Just interested in other DBA's idea's & solutions!
Thanks,
Clive Strong
September 26, 2002 at 9:37 am
If You have installed MS SQL client tool on your box, You can use it to create Database alias, otherwise follow my second reply.
Amir
quote:
Hi all,I'm curious to know how people move DTS Packages between Servers. For example moving between Dev, QA & Prod. Do you simply Save the DTS to another package then go through and rename the Connections?
I started using Dynamic Properties for this kind of thing and it works well for me (seperate INI files on each server with the connection parameters).
Just interested in other DBA's idea's & solutions!
Thanks,
Clive Strong
September 26, 2002 at 12:01 pm
We alias Server Connections where I work, as we have Dev, Stage, QA, and Prod enviroments. It works well for us, and provides consistency throughout all coding for the databases, not just for DTS.
January 4, 2005 at 12:28 pm
I use ActiveX Scripts to configure packages for target environments. I also use them to fail packages when run by other than the configured target machine.
They have been very easy to create and useful in change management. The only hangup is with changing task names, as this makes the task uneditable. (Known Bug)
The script basicly references the package object, sets a global varaible for the target server, changes the log settings, and then loops through all connections of like names and changes the server and or database for each connection.
They are easy to use. Just execute the script task, save the package, and the package is reconfigured.
I will post a sample script.
January 5, 2005 at 10:59 am
Clive,
I use DTS to move packages directly from msdb.sysdtspackages on one server to the same table on another server. I took the instructions at http://www.sqldts.com/default.aspx?204 and added a Dynamic Properties task to set the source and destination computer names.
Greg
Greg
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply