I've created a loop in a DTS, which retrieves connection information from a SQL table and then (is supposed to) populates tables in different Databases.

The loop works, but it keeps updating the same DB, rather than updating each of the individual DBs. The connection information seems to be updating each time the loop goes around, but the data isn't going into the correct DB.

any ideas?


code:
using the ideas from:

http://www.sqldts.com/default.aspx?213
http://www.sqldts.com/default.aspx?214,2


'************************************************* *********************
' Start of Loop
'************************************************* ***********************

Function Main()

set oPackage = DTSGlobalVariables.parent
set stpEnterLoop = oPackage.Steps("DTSStep_DTSExecuteSQLTask_1") 'Insert table
set stpFinished = oPackage.Steps("DTSStep_DTSExecuteSQLTask_17") 'Finish

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

If not oRS.EOF Then

stpEnterLoop.DisableStep = False
stpFinished.DisableStep = True
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting

msgbox oRS.Fields(3).Value


for each packageConn in oPackage.connections
if packageConn.ProviderID = "SQLOLEDB" then
if packageConn.Name = "SQL Connection" then
packageConn.datasource = oRS.Fields(2).Value
packageConn.Catalog = oRS.Fields(3).Value
packageConn.UserId = oRS.Fields(4).Value
packageConn.Password = oRS.Fields(5).Value
end if
end if
next


oRS.MoveNext

else
stpEnterLoop.DisableStep =True
stpFinished.DisableStep = False
stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
End if

Set oRS = Nothing

Main = DTSTaskExecResult_Success
End Function







'************************************************* *********************
' Loop Around
'************************************************* ***********************

Function Main()

set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3")
stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

Main = DTSTaskExecResult_Success
End Function






---
Posted using Wimdows.net NntpNews Component -

Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Make sure you have set "Close Connection on Completion" in the workflow
properties of the step

--

----------------------------

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


<aengusd> wrote

Databases.
each time the loop goes around, but the data isn't going into the correct
DB.

Thanks Allan - that worked.

That's a pint I owe you!
Aengus

hi...could you guide me how to do to dts loop...


<aengusd> wrote

Databases.
each time the loop goes around, but the data isn't going into the correct
DB.