Hi,
I have a package that starts and ends with ActiveX tasks. The first task
locates a file and then enables the following step:
Set stpStart = pkg.Steps("DTSStep_DTSDataPumpTask_1")
stpStart.DisableStep=False
stpStart.ExecutionStatus = DTSStepExecStat_Waiting

The ending ActiveX task then does the same thing to the first ActiveX step.
This forms a loop until the first step can not find a file. The problem is
that the second time through the Loop the following datapump does not start
and the package ends with success. Nothing I do gets step #2 to run a second
time.

Activex1 - DTSStep_DTSDataPumpTask_1 - - - - ActiveX2 (go back to
Activex1)

any ideas how to get DTSStep_DTSDataPumpTask_1 to run the second time?

Is this a home grown version or is it ours?

www.sqldts.com/default.aspx?246

If our's then it will keep looping as long as there are files to be found.

You may want to try outputting the name of the file it finds each time.

Allan

"Snake" wrote:

Allan,
Mine is homegrown but remarkably like "yours." In fact, I got the basic
code from your site's examples. I used to msgbox to verify that the files
are are being found and that that the DataSource is being set properly for
the new file name. I can see that the start of the second pass through the
loop (your step 4) contains the second file name and that the "text file
(source)" is set correctly but the following datapump step (your 5) does not
start! Is there something in your model which works-around a bug or landmine
in DTS?

Michael



"Allan Mitchell" wrote:

Nope.

My step 5 will always work as long as

1. There are files
2. It is not disabled.



"Snake" <Snake (AT) discussions (DOT) microsoft.com> wrote

allan,
By adding Msgbox'x and extra ActiveX steps, I have verified that the looping
is indeed working OK! It also sets the DataSource properly (shows changed
value after package ends). The real underlying issues is that the package
believes it has completed successfully when it reaches the DataPump step.
The DataPump step simply will not run a second time. None of the DataPump's
Workflow settings are "checked." I am so close, yet so far!

"Allan Mitchell" wrote:

Have your second Active-X's workflow script set the first Active-X step in
a waiting state and set the return of the workflow step not to execute the
Task until there are no more files left for Active-X #1 to process.

Active-X Task Script #2 performs the cleanup routines and ends with a
successful return status when the package is complete.

-Mike Gercevich

--
Message posted via http://www.sqlmonster.com

Mike,
After much experimentation I finally have a functioning Looping DTS
package! The final change which caused the loop to function correctly was to
set the Workflow Option "Close Connection On Completion" to TRUE for the
Datapump step. Without this setting, the Datapump could not find its
modified DataSource. I do not recall any mention of this requirement in the
samples I read. . .

Michael

"Mike Gercevich via SQLMonster.com" wrote: