I have used sqldts.com as a source of the majority of my information
to create a DTS package that loops just like the example "Looping,
Importing and Archiving" however I am not using the file system, I am
using a list of servers.

I have a DTS package that loops through and saves data onto several
SQL Servers. The looping is working properly however I use the
following code to dynamically change the data source and it is not
working. In fact all the data is going to the original server. I
don't understand what is missing.

Thanks,
aschlitt

Function Main()
Dim rst
Dim con, strcon
Dim pkg
Dim stpEnterLoop
Dim stpFinished
Dim conServer
Set pkg = DTSGlobalVariables.Parent
Set stpEnterLoop = pkg.Steps("DTSStep_DTSExecuteSQLTask_5")
Set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
Set conServer = pkg.Connections("Tracker")
Set con = CreateObject("ADODB.Connection")
strcon = "Provider=sqloledb;Server=gkpssql;Initial
Catalog=XSpec-Test;User Id=sa;Password=itsok;"
con.open strcon
Set rst = con.Execute("SELECT Server FROM GK_TrackerDestination
WHERE (SendNow = 1) AND (Complete = 0))
If rst.EOF Then
'All done - Stop processing
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success

Else
If DTSGlobalVariables("gv_SiteId").Value = rst("SiteId") Then
'In a loop where one site will not finish
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Failure
Else

'Have at least one site left to do
stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
DTSGlobalVariables("gv_Server").Value = rst("Server")
conServer.DataSource = rst("Server")
Main = DTSTaskExecResult_Success
End If
End If
rst.close
con.close
Set rst = Nothing
Set con = Nothing
Set pkg = Nothing
Set stpEnterLoop = Nothing
Set stpFinished = Nothing

End Function

I couldn't find your .MoveNext of the recordset object to iterate through
your servers

something like

WHILE NOT Rs.EOF

..
...
....

Rs.MoveNext

WEND

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"A. Schlitt" <amschlitt (AT) yahoo (DOT) com> wrote

I have sent you a sample package directly, but basically you can use an
Execute SQL Task to run the query and return a ADO recordset as a "rowset
variable". You can drive the loop from this recordset.


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

"A. Schlitt" <amschlitt (AT) yahoo (DOT) com> wrote

The move next is missing because of the way I am looping. Toward the
end of my loop I make complete = 1 so the next time it goes through the
code I presented, that previous site won't be in my recordset.

Do you see anything else? Thanks!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If the connection does not update as expected, make sure you have set the
Close Connection on Completion option under workflow properties for the
DataPump task, and any other tasks that use that connection, or at least the
last task inside the loop.

Darren

"Andrea Schlitt" <anonymous (AT) devdex (DOT) com> wrote