dts loop to change server connection not working

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: DBA72 (DBA72_at_discussions.microsoft.com)
Date: 12/03/04 

Date: Thu, 2 Dec 2004 23:39:03 -0800

I have a dts loop that is supposed to loop through a list of servers and then
change the connection in the dts package for each server in the list. The
loop seems to work fine but the server connection properties always stay on
whatever server is first in the list. Any ideas why this is not changing for
each server. Here is my activex script:
BEGIN LOOP:
Option Explicit
Function Main()
        
        dim oPkg
        dim stpEnterLoop
        dim stpFinished
        dim oConnection
        dim oDataPump
        dim oDataTask

        set oPkg = DTSGlobalVariables.Parent
        set stpEnterLoop = oPkg.Steps("DTSStep_DTSActiveScriptTask_3")
        set stpFinished = oPkg.Steps("DTSStep_DTSActiveScriptTask_4")
        set oConnection = oPKG.Connections("RemoteServer")
        set oDataPump = oPKG.Tasks("DTSTask_DTSDataPumpTask_1")
        set oDataTask = oDataPump.CustomTask

        Dim oRS
        Set oRS = DTSGlobalVariables("gv_ServerList").Value

        if ShouldILoop = True then
                stpEnterLoop.DisableStep = False
                stpFinished.DisableStep = True
                
                ' Feed fist row into variables
                DTSGlobalVariables("gv_ServerID").Value = oRS.Fields(0).Value
                DTSGlobalVariables("gv_ServerName").Value = oRS.Fields(1).Value
                DTSGlobalVariables("gv_LastRun").Value = oRS.Fields(2).Value
                oConnection.DataSource = DTSGlobalVariables("gv_ServerName").Value
                oConnection.Catalog = "AdminDB"
                oDataTask.SourceSQLStatement="EXECUTE AdminDB.dbo.uspGetAllertPool
@LastRun='" & DTSGlobalVariables("gv_LastRun") & "',@ServerID=" &
DTSGlobalVariables("gv_ServerID")
                
                stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
                
        Else
                MSGBOX "ShouldLoopFalse:" & DTSGlobalVariables("gv_ServerID").Value
                stpEnterLoop.DisableStep =True
                stpFinished.DisableStep = False
                stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
        End if

        Set oRS = Nothing
        Set oConnection = Nothing
            Set oPKG = Nothing
        Set stpEnterLoop = Nothing
        Set stpFinished = Nothing
        Set oDataPump = Nothing
        Set oDataTask = Nothing

        Main = DTSTaskExecResult_Success
End Function

Function ShouldILoop
        Dim oRS
        Set oRS = DTSGlobalVariables("gv_ServerList").Value

        ' Check if there are still more records in the Recordset
        If oRS.EOF Then
                ' No more records, so continue workflow
                ShouldILoop = CBool(False)
        Else
                ShouldILoop = CBool(True)
        End If

        Set oRS = Nothing
End Function

LOOP AROUND:
' 246 (Loop Around)
Option Explicit

Function Main()
        
        Dim pkg
        Dim stpbegin
        Dim oRS
        
        set pkg = DTSGlobalVariables.Parent
        set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3")
        set oRS = DTSGlobalVariables("gv_ServerList").Value
        
        ' Move to the next row in preparation for loop iteration
        oRS.MoveNext
                
        stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

        set oRS = Nothing
        set pkg = Nothing
        set stpbegin = Nothing

        Main = DTSTaskExecResult_Success
End Function

Relevant Pages

  • Re: Emergency! Too Busy A Connection!
    ... > a number of clients that need data from my Accessdatabase. ... The problem is that when the connection is retrieving data for ... > the loop, ... Server or another DBMS better suited to your environment and concurrent user ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Threading and returning values
    ... The e.Result is not coming out of the loop. ... I have now got my SMTP server apparently running correctly now, ... BackgroundWorker bw = new BackgroundWorker; ... // Note that in the Click event handler, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Problem with OnReceive method
    ... > I' m developing a multithread application with a socket network ... > continuosly Receive in the thread socket loop. ... > this one start the server thread. ... when a connection arrived it start a new childthread and ...
    (microsoft.public.vc.mfc)
  • Re: Brian Kernighan, maybe Im not worthy, maybe Im scum
    ... If you think I'm giving a verbose pseud write access to my server, ... interface appears in C Sharp as consisting of sbyte arrays. ... failed to see that a for loop is not just a while loop because a while ... chilling effect on freedom of speech. ...
    (comp.programming)
  • Re: logging.SocketHandler connections
    ... this behaviour when the server closes the connection. ... there is a while loop which you have left out ... chunk = self.connection.recv ...
    (comp.lang.python)