Dynamic Properties Task

Question

Post reply

Dynamic Properties Task

Abid Malik

Valued Member

Points: 68
More actions
June 28, 2005 at 2:07 pm

#60151

I want to change the Data Source Property of Connection Object in every DTS Package (using Dynamic Properties Task). I have over 100 DTS Packages and I want to change the Source Server name in Connection Object.
How can I do this? I do not want to open each DTS package one by one and do it. Is there any way do it on the fly?
Thanks for your help or even any suggestion.
Abid Malik

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ND-215452 SSCrazy Points: 2037 More actions · Answer 1

What I did was:-

1.create a data.INI file which contains something like this:

[section] --name of the connection

server=servername

dbase=dbname

uid=username

pwd=password

[section2]

.....

2. load this file into dynamic properties task.

when you excute this step you will see servername, dbname etc changed on the connection source.

philcart SSC-Forever Points: 47794 More actions · Answer 2

You'll need to write a short VBScript to bulk change all your packages. Take a look at my script over here and modify as necessary,

http://www.sqlserver.org.au/resources/ViewResource.aspx?resourceId=40

For setting up your packages to perform the change 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

--------------------
Colt 45 - the original point and click interface

Abid Malik Valued Member Points: 68 More actions · Answer 3

Hi Phillcart,

That was imazing script. I have run the script and did get the file where all my Connection Object and Data Source are showing. But Now how do I change them?

Your script find out the values but do not change them in DTS Package itself and this is what I needed to do!

How can I achieve it? Can you go ahead and update the MSDB database with new value?

how can I utilize your script to change value.

Thanks again

Abid

Rajesh Hegde Right there with Babe Points: 761 More actions · Answer 4

You can write a .NET program or VB program that would dynamically load the .NET package and use the DTS object model to manipulate the data source property of each connection. You might also be able to save the modified DTS package to Database or .dts file.

Hope this helps.

philcart SSC-Forever Points: 47794 More actions · Answer 5

From my script you can see what properties I'm using to get the information. You just need to modify the script to update those properties to the new values you specify.

    sLine = sLine & sConn.Datasource & "," <-- the server name
    sLine = sLine & sConn.Catalog & "," <-- the database name
    sLine = sLine & sConn.UserID & "," <-- the userID if specified

So these would become something like,

    sConn.Datasource.Value = "NewServer"
    sConn.Catalog.Value = "NewDatabase"
    sConn.UserID = "NewUserID

--------------------
Colt 45 - the original point and click interface

Abid Malik Valued Member Points: 68 More actions · Answer 6

Hi Phillcart and Rajesh,

Thanks a bunch. Yes I was able to got it. wow. I tried on 3 packages and it worked fine.I am planning to apply this on 240 DTS Packages now..

Thanks again for your help!

Abid Malik