I have a DTS package that extracts data from a Sybase 11 datasource into a
holding table in SQL Server 7 using a data-pump.

The Sybase data-source is meant to return something in the region of
1million records. Because of a large date range from the query in Sybase,
even putting it into a Stored Procedure isn't much help, and we have little
choice but to re-run the Stored Procedure in Sybase multiple times for
smaller date range.
How could I do this dynamically? The Data source is something like
EXEC p_dataExtractor 20010101,20050331

I want to reduce the date range and called it multiple times. I know I can
do something like the following to change the DataSource's SQL

' Get a handle
Set oCustomTask = oPackage.Tasks( "DTSTask_DTSDataPumpTask_1").CustomTask

sSQL = oCustomTask.SourceSQLStatement

'Call SetDateParams to subsitute templated date parameters with actual date
parameters
SetDateParms sSQL, _
DTSGlobalVariables( "gvStart_dt").Value, _
DTSGlobalVariables( "gvEnd_dt").Value)

oCustomTask.SourceSQLStatement = sSQL

But what can I actually do to repeat the process &/or to re-wire the process
(rather than having a long chain of static sequential pumps)??

Just had an idea but not sure how this would work. Can I use the Workflow
Properties->Active-X Script's Main Function to alter the task to get it to
re-run (and change the date-parameter from within the Workflow's VBScript)?

Currently, the parameters are set in a task prior to the data-pump task.


"Patrick" <patl (AT) reply (DOT) newsgroup.msn.com> wrote

Here is a quite a simple example that demonstrates looping-

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


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

"Patrick" <patl (AT) reply (DOT) newsgroup.msn.com> wrote

I had a quick go, seems like this works:
Function Main()
If DTSGlobalVariables("RSTables").Value < 5 Then
Main = DTSStepScriptResult_RetryLater
Else
Main = DTSStepScriptResult_ExecuteTask
End if
End function

but following the example as follows seem to only execute the task once!
Function Main()
If DTSGlobalVariables("RSTables").Value < 5 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End if
End function

Am I doing it wrongly??

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

OK! I figured out that the example at http://www.sqldts.com/default.aspx?298
actually does the loop/repeat at the DisplayMsgBox task to "re-wire" it back
to BuildMsgBoxString.

I have the following in my example:
1) Preliminaries ActiveX task, currently only
Function Main()
Main = DTSTaskExecResult_Success
End Function
2) DTSStep_DTSDataPumpTask_2 task:
Transform from a dummy Excel97 file(DataSource=C:\dtsTest.xls) to a table
in SQL Server dbo.dbTest.tblTest
3) The Workflow Properties for the Pump task has the following ActiveX Script:
Function Main()
If DTSGlobalVariables( "gvNoOfAttempts").Value > 5 Then
Main = DTSStepScriptResult_ExecuteTask

else
DTSGlobalVariables( "gvNoOfAttempts").Value = CInt(DTSGlobalVariables(
"gvNoOfAttempts").Value) + 1
DTSGlobalVariables.Parent. _
Steps("DTSStep_DTSActiveScriptTask_1"). _
ExecutionStatus = DTSStepExecStat_Waiting

MsgBox DTSGlobalVariables( "gvNoOfAttempts").Value
Main = DTSStepScriptResult_DontExecuteTask
End if


End Function

Observations:
1) When DTS is executed, it MsgBoxes 2,3,4,5,6 as expected
2) Only one row is loaded into the destination table!! :-(
3) Doing the following doesn't make a difference:
3.1) Changing the package properties to NOT use transactions
3.2) Changing the DataSource after the first interation:
DTSGlobalVariables.Parent.Connections.Item(2).Data Source="c:\dtsTest2.xls"

(It then just loads whatever is in c:\dtsTest2.xls into the target table,
ignoring data in c:\dtsTest.xls)

HELP!

"Darren Green" wrote: