can't get DTS package or step to fail

[broniusm]

I can't get my DTS to fail, so here's my question to you: Are there tasks in a DTS that can cause a fail to be overridden?

For instance:
-a DTS package with a single SQL task in it with only the SQL statement:
RAISERROR ('FailThis', 16, 1)
..will fail the package nicely: I get a big red X, the message "FailThis" and the package stops execution.

But, the package with which I'm working, which has many twists and workflows in it, should fail on that same step as well, but it does not. Nor will it stop on the first task to get executed when explicitly I add to the SQL statement an error raise:

/* existing SQL: */
Select * from EDITask
where TaskName = 'DataExport';
/* added error raise */
RAISERROR ('BroniError', 16, 1)

Rather than failing, the thing just executes along its merry way, never missing a beat.


Any ideas?? Any experience with this to help me go in a general direction (besides over the edge)??

 

[nigelrivett]

Set the package property
Fail package on first error (under logging)

or set
Fail package on step failure
for each step.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[broniusm]

I've tried fail package on step failure and fail on first.

I think I have a lead, though: if I separate the SQL statements within each block with "go" it seems to react to the raiserror. This is a good thing, but now I've got the difficulty of reading my error returned variable:

like:
execute @resultcode = sp_uFTP ?..
go
--check @resultcode and report error
go

Unfortunately, however, this won't work b/c after an sql GO statement, scope resets and @resultcode is no longer declared nor defined.

I am now toying with output variables and/or global variables.

 

[MDXer]

You could break the SQL statement into smaller tasks and make each one a seperate step in the DTS package. Set your work flow to On Success to move from one piece to the next. The make an active X task that is your error handler that has a work flow property of ON fail.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[broniusm]

MDXer-
That did it. I've not got an sql staement to call FTP, and without assigning any return values from that stored procedure call or anything, the output is automatically transferred as an output variable when I asked it to. My ActiveX script checks that string for either "" or "success", anything else passes workflow as a Fail.

Thanks for your quick responses.
-bronius