November 11, 2003 at 5:37 am
I have a DTS package which loads and processes data from files in a directory.
The first step (an ActiveX task) gets a list of files from a directory and for the first file it archives it and then exits.
The second and subsequent steps load the file into a temporary table, reads, processes, validates the data and then updates the ultimate database tables.
So that the process will continue for all the files in the directory, the final step sets the status of the first step to waiting so that the whole process starts again.
This is working OK but I had a problem when there were not more files to process. I put in the line:
stpContinuePkg.DisableStep = True
to prevent the second step executing. If I find a file in the directory I set:
stpContinuePkg.DisableStep = False
which should mean that Step2 executes. There is an OnSuccess workflow from Step 1 to Step 2.
The problem is that the second step is not executing.
Has anyone any ideas why step 2 is not executing?
Thanks
Jeremy
November 11, 2003 at 10:29 am
Try setting the step to successful when all files have been loaded.
Steve Hughes
Magenic Technologies
November 12, 2003 at 6:17 am
Hi,
Best is to work with the Step result constants, not the step Disable/Enable properties.
This proc resets the "to do" list of the DTS engine, making it jump back to the previous steps. Once the condition is met, DTS will go to the next step because the previous steps are now "Execute Success".
With your solution, the step is disabled when DTS arrives there. DTS skips the step and sets the status to "Executed"
When you enable it afterwards, DTS will never start the step because it has already been started by DTS!
In the Workflow properties, use ActiveXscript.
If files=done then
'Exit this workflow naturally and go to next step
Main = DTSStepScriptResult_ExecuteTask
Else
'Start the import for the next file
'redo the previous steps in the workflow
set lobjPackage = dtsglobalvariables.parent
lobjPackage.steps "DTSStep_DTSDataPumpTask_1").executionstatus = DTSStepExecStat_Waiting
lobjPackage.steps("DTSStep_DTSDataPumpTask_2").executionstatus = DTSStepExecStat_Waiting
Main = DTSStepScriptResult_ExecuteTask
End if
November 12, 2003 at 6:19 am
More questions?
Then you might consider buying this book:
SQL Server 200 DTS, Wrox editions
The tip comes from this book.
November 12, 2003 at 8:59 am
you should have a final and an initial steps
The Initial should findout how many files in total and save that in a global variable.
The final should check for the the file count
FINAL STEP LOGIC :
IF
it is not all done
1. increment (or decrement depending on your logic) the global variable
2. set all but the first step to WAITING
ELSE
(the count has reach it's end)
1. return EXECUTE SUCCESS.
END
I hope it is clear enough
* Noel
November 18, 2003 at 4:35 pm
I agree with noeld. Place the code in a workflow activeX script on the final task (decide whether or not to loop)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply