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

On the surface it looks OK to me. Is the server being changed but not taking
effect? This is common issue which is solved by setting the Close connection
on completion for the last task that uses the connection. This forces the
connection to be reopened which means it picks up the new details.


--
Darren Green
http://www.sqldts.com

"DBA72" <DBA72 (AT) discussions (DOT) microsoft.com> wrote