DTS with multiple loops - HELP!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS with multiple loops - HELP!

Author

Topic

mhanly
Starting Member

1 Post

Posted - 2004-07-22 : 15:40:26

Hey all,
Im somewhat new to anything but the simplest DTS routines. Heres my problem: I have a somewhat slow ODBC connection to one of my companies systems, and Im trying to pull data straight from that ODBC connection into SQL. Not a problem. I have a list of clientID#s that I need to report on, and I have sucessfully set up a DTS that reads the clientID#s from a local table, imports and transforms, then loops around and proceeds until the clientID# recordset is empty. I used this: http://www.sqldts.com/default.aspx?298 and this: http://www.sqldts.com/default.aspx?246 as examples. I find that the imports go MUCH slower if I try to grab any more than one clientID# at a time in the same query, but not at all slower if I run 4 at the same time each on different clientID#s. So Im trying to add some "parallel" imports in my package. I want to have at 4 connections, each in a logically seperate loop but all reading from the same recordset. My problem is that I always have to wait for all the loops to complete before any of the others can go back around and get another record. I dont even know what the real term for what Im trying to do is, but "parallel loops" is the best thing I can come up with. Can anyone point me to a good article or site that can help me with this? Or tell me what Im doing wrong? I pretty much have the same layout as the article here http://www.sqldts.com/default.aspx?246 , except that I have 4 lines between the "Begin Loop" and "Loop Around". Heres my code for the Begin loop and End loop steps:

Thanks,
M

Start Loop Code:
Function Main()

dim pkg
dim stpMoveAccount
dim stpTransform1
dim stpTransform2
dim stpTransform3
dim stpTransform4
dim oRS
set oRS = DTSGlobalVariables("rsClients").Value
set pkg = DTSGlobalVariables.Parent
set stpMoveAccount = pkg.Steps("DTSStep_DTSExecuteSQLTask_2")
set stpTransform1 = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpTransform2 = pkg.Steps("DTSStep_DTSDataPumpTask_2")
set stpTransform3 = pkg.Steps("DTSStep_DTSDataPumpTask_3")
set stpTransform4 = pkg.Steps("DTSStep_DTSDataPumpTask_4")

if oRS.EOF = True Then
'Recordset is empty, proceed to Move_Account_Load if all transforms arent active
if (stpTransform1.ExecutionStatus = DTSStepExecStat_InProgress) or (stpTransform2.ExecutionStatus = DTSStepExecStat_InProgress) _
or (stpTransform3.ExecutionStatus = DTSStepExecStat_InProgress) or (stpTransform4.ExecutionStatus = DTSStepExecStat_InProgress) then
'some transform is still active
'disable any transforms that arent active

stpMoveAccount.DisableStep = True
if (stpTransform1.ExecutionStatus <> DTSStepExecStat_InProgress) Then
stpTransform1.DisableStep = True
end if
if (stpTransform2.ExecutionStatus <> DTSStepExecStat_InProgress) Then
stpTransform2.DisableStep = True
end if
if (stpTransform3.ExecutionStatus <> DTSStepExecStat_InProgress) Then
stpTransform3.DisableStep = True
end if
if (stpTransform4.ExecutionStatus <> DTSStepExecStat_InProgress) Then
stpTransform4.DisableStep = True
end if

else
'no transforms active

stpTransform1.DisableStep = True
stpTransform2.DisableStep = True
stpTransform3.DisableStep = True
stpTransform4.DisableStep = True
stpMoveAccount.DisableStep = False
stpMoveAccount.ExecutionStatus = DTSStepExecStat_Waiting
end if

else
'Still more records to process, move to next row and find an available transform

if (stpTransform1.ExecutionStatus <> DTSStepExecStat_Waiting) and (stpTransform2.ExecutionStatus <> DTSStepExecStat_Waiting) _
and (stpTransform3.ExecutionStatus <> DTSStepExecStat_Waiting) and (stpTransform4.ExecutionStatus <> DTSStepExecStat_Waiting) Then
msgbox "Some sort of workflow error - looking for an open transform and cant find one"
Main = DTSTaskExecResult_Failure
exit function
end if

stpMoveAccount.DisableStep = True
if (stpTransform1.ExecutionStatus = DTSStepExecStat_Waiting) and oRS.EOF = False Then
DTSGlobalVariables("strClient1").Value = oRS.Fields(0).Value
oRS.movenext
end if
if (stpTransform2.ExecutionStatus = DTSStepExecStat_Waiting) and oRS.EOF = False Then
DTSGlobalVariables("strClient2").Value = oRS.Fields(0).Value
oRS.movenext
end if
if (stpTransform3.ExecutionStatus = DTSStepExecStat_Waiting) and oRS.EOF = False Then
DTSGlobalVariables("strClient3").Value = oRS.Fields(0).Value
oRS.movenext
end if
if (stpTransform4.ExecutionStatus = DTSStepExecStat_Waiting) and oRS.EOF = False Then
DTSGlobalVariables("strClient4").Value = oRS.Fields(0).Value
oRS.movenext
end if

end if

Main = DTSTaskExecResult_Success

End Function

End Loop Code:
Function Main()

'nothing to do here but make the start of the loop the destination

dim stpTransform1
dim stpTransform2
dim stpTransform3
dim stpTransform4
dim pkg, stpStartLoop
set pkg = DTSGlobalVariables.Parent
set stpTransform1 = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpTransform2 = pkg.Steps("DTSStep_DTSDataPumpTask_2")
set stpTransform3 = pkg.Steps("DTSStep_DTSDataPumpTask_3")
set stpTransform4 = pkg.Steps("DTSStep_DTSDataPumpTask_4")
set stpStartLoop = pkg.steps("DTSStep_DTSActiveScriptTask_2")

if stpTransform1.ExecutionStatus = DTSStepExecStat_Completed then stpTransform1.ExecutionStatus = DTSStepExecStat_Waiting
if stpTransform2.ExecutionStatus = DTSStepExecStat_Completed then stpTransform2.ExecutionStatus = DTSStepExecStat_Waiting
if stpTransform3.ExecutionStatus = DTSStepExecStat_Completed then stpTransform3.ExecutionStatus = DTSStepExecStat_Waiting
if stpTransform4.ExecutionStatus = DTSStepExecStat_Completed then stpTransform4.ExecutionStatus = DTSStepExecStat_Waiting

stpStartLoop.ExecutionStatus = DTSStepExecStat_Waiting

Main = DTSTaskExecResult_Success

End Function

nr
SQLTeam MVY

12543 Posts

Posted - 2004-07-25 : 18:06:31

I would probably do this with 4 scheduled tasks (or however many you want) calling an SP which calls a dts package with the client ID to retrieve the data - controlling by an entries in a table. In that way the data retrieval is kept separate and can easily be changed without affecting anything else.

I would guess that your workflow is putting a dependency on the loop control of all the 4 tasks rather than any of them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources