sql server dts: dts loop problem -- sql server dts

dts loop problem

aengusd
6/25/2004 1:53:17 AM

sql server dts:

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 -

Re: dts loop problem

aengusd
6/25/2004 2:33:27 AM

Thanks Allan - that worked.

That's a pint I owe you!
Aengus

[quoted text, click to view]

Re: dts loop problem

Allan Mitchell
6/25/2004 10:26:03 AM

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

[quoted text, click to view]
a SQL table and then (is supposed to) populates tables in different
Databases.
[quoted text, click to view]
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.
[quoted text, click to view]
supports Post Alerts, Ratings, and Searching.

Re: dts loop problem

PK
7/7/2004 12:06:28 PM

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

[quoted text, click to view]
a SQL table and then (is supposed to) populates tables in different
Databases.
[quoted text, click to view]
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.
[quoted text, click to view]
supports Post Alerts, Ratings, and Searching.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.