×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Changing connection datasource in loop

Changing connection datasource in loop

Changing connection datasource in loop

(OP)
I have a DTS package created for SQL 2000 which should go out to 36 remote locations, query some data and import it into a central database.

I have this set up as a loop within the DTS package using a rowset to control the looping.  The rowset contains all my remote server names and the loop keeps going until I've looped through all records.

My problem is that even though I change the connection.DataSource during the processing, the connection still always points to the location it was initialized with.

It would appear I need to force the connection closed perhaps, then set the datasource then re-open the connection.

The code I wrote in the ActiveX script looks like:

CODE

Set oPackage = DTSGlobalVariables.Parent

Set oSourceCL = oPackage.Connections("SourceCL")

oSourceCL.DataSource = sSourceDB
oSourceCL.Catalog = "Trinidad"
oSourceCL.UserID = "sa"
oSourceCL.Password = ""
'the messagebox correctly shows for all 36 remote locations
msgbox oSourceCL.DataSource
During execution, if my servers were named Server1 - Server 36, the message box would correctly pop up for every server(1 - 36).  If I created the package with the connection set as Server1 however, this is the only server that is queried and it is queried 36 times.  After the package completes, if I go back into the designer for the connection and look at its properties, its datasource is now set to the last server in the list(i.e. Server36).

So the datasource is changing, it is just being ignored.  Is there something I need to do to force close the connection so it will reinstantiate with the new datasource.

TIA!

RE: Changing connection datasource in loop

I haven't used DTS in a while, but I think what you're looking for is the "Close Connection On Completion" setting under your Transformation task's workflow properties.

Hope this helps,

Alex

----signature below----
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

RE: Changing connection datasource in loop

Glad you got it working.  That has burned me at least once in the past :)

----signature below----
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close