Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: Need DTS/VBScript guru

    I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.

    While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.

    Anyone out there able to successfully change a connection object inthis manner??

  2. #2
    Join Date
    Feb 2004
    Posts
    10
    I figured it out myself but just in case anyone else has this problem:

    In order to use activeX to change a connection object DataSource/Catalog (server/database) from one SQL server to another one (different physical box) via ActiveX Script task you must set the RESUABLE property to 0 rather than the value -1 as mine had defaulted to. Now it all works like a charm....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •