Stopping a DTS Package..
-
We have a DTS package that has many steps to it. We would like to add a step that stops the execution of the package based on the results of a query etc.
How do we go about this?
-
Hello Carl,
I have done this using the raise error t-sql command. This will cause the packages to error in most cases.
Have a look at online books for details.
Myles
-
Not sure exactly what you're looking for, but you should probably be able to find the answer here: http://www.sqldts.com/default.aspx?103 and if not, at least get some ideas.
David -
Carl: If you can do it, add an ActiveX module with a 'MSGBOX()' call in it. Just put it in between/alonside the other modules.
If you are using Global Variables, pass them to it & put them into the MSGBOX call.
Good Luck
Robbie D
Sunny Times.... -
Add an ActiveX Script which would check the results of the query in question. Then use logic to test for the criteria you're wanting to stop execution...
If x Then
Main = DTSTaskExecResult_Failure
Exit Function
Else
'Continue
End If
-
bellis' solution is the way to go. You could add a sql task and store the resultset to a global variable.
An example of a query that I use to do this.
IF EXISTS (SELECT ID FROM DbName.owner.TableName WHERE DATEDIFF(DAY, getDate(), createdate) = 0 )
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
You could then set "x" in bellis' solution equal to the global variable that holds the result of your query.
Good Luck!
-
Just adding a little more to the bellis' solution....
I hated it that every time my package wasn't meant to execute any further (due to the true/false, 0/1 etc) returned to a global variable (use output params) that the entire package was stopped with failure (from bellis' contribution ->
Main = DTSTaskExecResult_Failure ), that I added the check on the global var to a workflow script. The difference being that you can then call a 'don't execute' on the task prior to it being started, and then the package completes with success but your susbsequent steps don't get run/executed.
The only downside I have found with this is that you realy need to either document your package well (externally) or include a text annotation in the package to let people know that the ActiveX script is there on the workflow.
The script I used was similar to ->
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
' Check the PreviousLoadStatus, if 0 then previously loaded, stop further work. If 1, then continue working.
Function Main()
if (DTSGlobalVariables("PreviousLoadStatus").Value = 1) then
Main = DTSStepScriptResult_ExecuteTask
else
Main = DTSStepScriptResult_DontExecuteTask
end if
End Function
'End script
Steve.
-
From BOL...
DTSStepScriptResult
The DTSStepScriptResult constants specify return codes to be used from the Microsoft® ActiveX® scripts associated with package steps. They should not be returned from the scripts associated with an ActiveXScriptTask object or DataPumpTransformScript or DTSTransformScriptProperties2 transformations.
Constant Value Description DTSStepScriptResult_DontExecuteTask 1 Do not execute task. DTSStepScriptResult_ExecuteTask 0 Execute task. DTSStepScriptResult_RetryLater 2 Retry execution later. -
Maybe it wasn't clear, if you re-read my original post, I don't use the Script result constant within the ActiveXScriptTask (I don't think it would work, this would require a result constant along the lines of Main = DTSTaskExecResult_Failure or success etc etc, ie these require Task constants). I use this script within the Workflow properties (which allows entry of activeX scripts), which is a step script rather than a task script, and as such allows the use of the Stepscript result constants, which in turn allows you to not execute the following task.
Steve.
-
Where (at what point) do you set your global variable?
-
Say you have a T-SQL task linked by workflow to some other task (say activeX), right-click the second task in the workflow, select Workflow|Workflow Properties. On the second tab (Options) of the workflow properties dialog there's a 'use ActiveX Script' checkbox, check it and then click the Properties button to add the script posted earlier.
Notice on the default script that the Main result is set to one of the step constans not a Task constant.
Steve.
-
Hey bellis, soory, misread your question... I would normally set my global var at a TSQL task earlier in the workflow. So workflow might go like [do something generic] -> use TSQL to set global -> DummyActiveXScriptTask (this is where I would set tthe workflow Activex script to check global's value) -> next step that relies on positive value in global.
Having tried to describe that I know see why we use GUI's
Steve.
-
Right, I found that. So, you're setting the global variable based on the results of the T-SQL Task, then...
T-SQL >>> Set global variable based on results >>> check value of global variable in the workflow script >>> if criteria is met, then DTSStepScriptResult_DontExecuteTask
-
I think we're on the same page.
I also think that our latest replies got crossed in the mail.
-
Basically yes, that's the way it was hanging together.
And yes, i think they crossed.
Just out of interest, I was doing this because I had to use someone else's package architecture that polled (every 15 minutes) and loaded the contents of a file (which may have been yesterdays data [bad] or new data [good]). I hate polling, so I've now re-written the package to use MSMQ (it waits on a message) and I post a message to the queue based on the file changing/being created etc. I wrote up a quick windows service to post the message based on file changes/creation/deletion etc. (For slightly more detail see the Yukon DTS forum)
Personally I'm a lot more confortable with this approach as there isn't a "hidden" script involved, and the package just sits (running) waiting for the message to hit the queue. If the msg doesn't get there within a specific timeframe, I've set the MSMQ reader task to timeout, so people get notified that "things" haven't worked. But then again, this is good for my situation where the system relied on text file imports.
Steve.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply