How can I loop the rowset values one by one for data connection? I've only checked if there're values in the recordset and assign the connection values as follows. I would like to ask where I can assign the execution status (stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting) to loop back for next recordset and exit the loop if the recordset is finished.

If not oRS.BOF The
Set oConn1 = DTSGlobalVariables.Parent.Connections("Sybase ASE OLE DB Provider"
oConn1.DataSource = Trim(oRS.Fields(0).Value) ' source server
oConn1.Catalog = Trim(oRS.Fields(1).Value) ' source D

Set oConn = DTSGlobalVariables.Parent.Connections("SQL Server"
oConn.DataSource = Trim(oRS.Fields(3).Value) ' dest. Serve
oConn.Catalog = Trim(oRS.Fields(4).Value) ' dest. D

Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTas
oDataPump.SourceObjectName = Trim(oRS.Fields(2).Value) ' source table
oDataPump.DestinationObjectName = Trim(oRS.Fields(5).Value) ' dest. tabl

Set oDataPump = Nothin
Set oConn = Nothin
Set oConn1 = Nothin

oRS.MoveNex
End I

Try this, as it si closer to what you want.

How to loop through a global variable Rowset
(http://www.sqldts.com/Default.aspx?298)

Replace step 2 with your DataPump. Note that step 2 has workflow script
which you need to change to include all of your connection and task setting
stuff, and put it back onto your task. This could be done in a Script Task
if you like.
See step 3 workflow code for where to set DTSStepExecStat_Waiting.

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

[quoted text, click to view]
checked if there're values in the recordset and assign the connection values
as follows. I would like to ask where I can assign the execution status
(stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting) to loop back for
next recordset and exit the loop if the recordset is finished.
[quoted text, click to view]

Do you mean that I could combine the step 2 workflow scripts into the task script
How about the step 3 workflow code? Could it be written into the last task script to loop back step 2

I don't know how to combine workflow script and task script together

I just use the example in http://sqldts.com/?246 and replace the connection stuff into step 2's task script. I could display the correct recordset values one by one. However, the data pump connection can only be completed at the last recordset. ie. I've 2 records in recordset. Only the last record's data are transformed to the destination location. I've also set the workflow properties's option = close connection on completion. What's the problem ? Do I need to add some steps or scripts to link up the connection

- replace e.g. 298 step 2's task scrip
Function Main(
Dim oR
Dim oConn, oConn1
Dim oPkg, oDataPum

Set oRS = DTSGlobalVariables("RSTables").Valu
Set oPkg = DTSGlobalVariables.Paren

MsgBox "Dest. DB:" & vbCrLf & oRS.Fields(4).Value & vbCrLf ' show correct value

Set oConn1 = DTSGlobalVariables.Parent.Connections("SQL Server 2"
oConn1.DataSource = Trim(oRS.Fields(0).Value) ' source server
oConn1.Catalog = Trim(oRS.Fields(1).Value) ' source D

Set oConn = DTSGlobalVariables.Parent.Connections("Sybase 1"
oConn.DataSource = Trim(oRS.Fields(3).Value) ' dest. Serve
oConn.Catalog = Trim(oRS.Fields(4).Value) ' dest. D

Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTas
oDataPump.SourceObjectName = Trim(oRS.Fields(2).Value) ' source table
oDataPump.DestinationObjectName = Trim(oRS.Fields(5).Value) ' dest. tabl

Set oDataPump = Nothin
Set oConn = Nothin
Set oConn1 = Nothin

oRS.MoveNex

Set oRS = Nothin
Main = DTSTaskExecResult_Succes