Re: How to force a SSIS package step to fail?


I've now tried to add an Expression to the Precedence Constraints, bit I have some troubles defining the Varible it's going to look at..:-(.

My Stored Procedure returns a character value ("Success" or "Failure"), but when I set the step to return the value to the type String, then I get an "String[0]: the Size property has an invalid size of 0" error everytime I execute the task.

Just for the test, I've tried to do the same when returning an INT value and that is working fine.

When I define the Variable in my packages as a String, I can't see that I can enter any size anywhere, so how do I get around this error?

Regards
Steen


Steen Schlüter Persson (DK) wrote:
Hi J,

I've also looked into this solution and it might also be what I'm going to do. I just thought that there was another way where I could force the step to fail and then use the "original" Success/Failure/Completion constrain.

Regards
Steen


jhofmeyr@xxxxxxxxxxxxxx wrote:
Hi Steen,

Why would you want the package to fail? In DTS (SQL Server 2000) you
could "fail" steps in order to control package flow, but his is not
necessary in SSIS. If you want the package to follow a different
control flow depending on the outcome of the stored procedures, you
can use conditional expressions based on the return values.

To do this, double-click on the connector line joining 2 of your
ExecuteSQL tasks and change the "Evaluation operation:" to any of the
options that include "Expression". This enables you to enter a
boolean expression that must evaluate to true for the path to be
followed. Assuming you've got an int parameter called "sp1rtnvalue"
which must evaluate to 1 for the flow to continue; your expression
would be:

@sp1rtnvalue == 1

Good luck!
J
.

Relevant Pages

  • Re: Use bind variables in a view
    ... >Is to basically wrap the view creation with a stored procedure, ... - define the variables in the package spec ... Alternatively (courtesy of Tom Kyte) you could set up a context, ...
    (comp.databases.oracle.server)
  • Re: I just want to run a stored procedure...
    ... I have a stored procedure that I want to run.The shell of it is ... PROCEDURE CODE0 (pCUR1 OUT CUR, ... You can't run a package, only the procedures and functions within it: ... I guess I just need to declare one? ...
    (comp.databases.oracle.server)
  • Re: remotely call a DTS
    ... I am not sure right now whether you get Access Denied on the package or Access Denied on something inside the package. ... This worked when I was logged into the server and ran it from my ... but the DTS blows up. ... The only time this stored procedure hsa worked was when I ran it ...
    (microsoft.public.sqlserver.dts)
  • Re: How to force a SSIS package step to fail?
    ... I just thought that there was another way where I could force the step to fail and then use the "original" Success/Failure/Completion constrain. ... Why would you want the package to fail? ... could "fail" steps in order to control package flow, ...
    (microsoft.public.sqlserver.dts)
  • Re: How to force a SSIS package step to fail?
    ... Why would you want the package to fail? ... could "fail" steps in order to control package flow, ...
    (microsoft.public.sqlserver.dts)