The way DTS works means that the loop is outside the ActiveX script.
Setting the DTSTransformStat_SkipFetch flag is effectively saying 'go back
to the start of the loop'.
So what you need is to keep a loop variable (for the number of days that you
want the loop to execute for) that will maintain its value between calls to
your ActiveX script. You do this by storing it as a Global Variable.
Goto the Package Properties dialog and add a Global vaiable of type Integer
and set its initial value to 0. Then your looping code should look
something like this:
iLoop = DTSGlobalVariables("LoopVar").Value
DTSDestination("OutputDate").Value = DateAdd("d", iLoop,
if iLoop = 6 then
DTSGlobalVariables("LoopVar").Value = 0
Main = DTSTransformStat_OK
DTSGlobalVariables("LoopVar").Value = iLoop + 1
Main = DTSTransformStat_OK + DTSTransformStat_SkipFetch
Hope this helps,
>I am trying to generate multiple output rows from a single input row using
>DTS ActiveX script. The script accepts a single row (with columns for a
>start and end date), then generates a row for each day within that range to
>a date dimension table. Although I can do this with VB, I am hoping that I
>can simply us DTS. I have a <<Do while ... Loop>> that generates each
>output row. The loop ends with the statement << Main =
>DTSTransformStat_SkipFetch >>. Main is the ActiveX function and I expect
>the DTSTransformStat_SkipFetch status to cause the data pump to write out
>the current output and simply use the current input for the next
>transformation. The ActiveX script loops through as expected but writes
>only the last transformation.
>Anyone knows what I am doing wrong? I appreciate any and all help.