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

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

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

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

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

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

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

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

Hi Steen,

There is no concept of "Length" in a package string variable - it is
as long as you make it.
Try deleting and re-creating the ExecuteSQL task. Sometimes when you
change a stored proc the metadata in SSIS doesn't update correctly.
You might also consider changing your SP into a function if all it's
doing is returning a value...

I think using expressions is a better method of controling application
flow than Succ/Fail/Comp ... what happens if your task were to
actually fail?

Good luck!
J

Hi Steen,

There is no concept of "Length" in a package string variable - it is
as long as you make it.
Try deleting and re-creating the ExecuteSQL task. Sometimes when you
change a stored proc the metadata in SSIS doesn't update correctly.
You might also consider changing your SP into a function if all it's
doing is returning a value...

I think using expressions is a better method of controling application
flow than Succ/Fail/Comp ... what happens if your task were to
actually fail?

Good luck!
J