Link to home
Start Free TrialLog in
Avatar of abilash_mathew
abilash_mathew

asked on

How to change connection string in DTS package

Recently my production server name has changed and wants to modify all the connections strings that I am using in my DTS packages.

Can any one tell me how can I change the connection string with minimal efforts?

When I tried it seems that I need to set the transformation for each action. Please let me know is there any better way to do that


Avatar of curtis591
curtis591

I do it by writing a vb program (or any language for that matter).  

Using the dts package object I load the package from the server.  Loop through the connection object, change the properties that I want.  Save the package and that is it.  
Private Sub MoveDTSPackages(pSourceServer, pPackage, pDestinationServer)
Dim dts_package As New DTS.Package2
dts_package.LoadFromSQLServer pSourceServer, , , 256, , , , pPackage
 
For z = 1 To dts_package.Connections.Count
  dts_package.Connections(z).DataSource = Replace(LCase(dts_package.Connections(z).DataSource), LCase(pSourceServer), LCase(pDestinationServer))
Next z
 
dts_package.SaveToSQLServerAs pPackage, pDestinationServer, , , 256
 
dts_package.UnInitialize
Set dts_package = Nothing
End Sub

Open in new window

Define a global variable for your server name and then use that to set the connection, otherwise two years from now you will have to do all of this again.
If the layout for your DTS package is not important to you and you don't care about versioning, than you can as a workaround use the first solution posted.
ASKER CERTIFIED SOLUTION
Avatar of abilash_mathew
abilash_mathew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>you just need to change connection string in one place and will be automatically replaced in other places<<
Yeah, that was really meaningful.

Moderator:
Please delete the question and refund points.  It has no place in the PAQ.

Thanks.