Force a SSIS pkg to FAIL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Force a SSIS pkg to FAIL

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-26 : 13:36:31

Does anyone know how to force a SSIS step to fail? I don’t want to export a file if no records were written.

I found this but not quite sure how to implement it:
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

Am I suppose to define a @sp1rtnvalue in the stored procedure that creates the file I want to monitor? Do a record count in the stored procedure. In my pkg I enter the expression @sp1rtnvalue == 1 when I run that step the expression will evaluate to false and fail?

If so where exactly do I enter the Boolean expression (@sp1rtnvalue == 1) , what tab in what dialog/wizard??

I hope that made sense.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-26 : 13:58:31

You have for each lines joining tasks called precedence constraint the property called Expression option (it has constraint,expression,constraint + expression). Select constraint + expression and check AND radio button on bottom. Then in experession box write your expression. The variable will be populated earlier by the step which checks count of records written.

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-27 : 08:27:47

Hi VISAKH16, thanks so much for the quick response. However I ran in to a problem. The error msg tells me my parameter was not found in the variable collection. Not sure what or where that is. Any help would be appreciated.

Here is what I coded:

Precedence Constraint Editor Values:
Expression Operation: Expression and Constraint
Value: Failure
Expression: @GotRecs == 1
Radio button: Logical AND. All contraints must evaluate to True

Stored Procedure has an IF (that contains a SELECT) statement that checks for records if no records are returned processing stops. I SET the parameter after the BEGIN that follows the IF statement.

The step that contains the above code is step #3. I placed the constraint between step #3 and step #4 (Data Flow task) just Exports to a Flat File.

When I clicked the TEST button in the Precedence Constraint Editor I got the following msg:
Error at Constraint 3: The variable “GotRecs” was not found in the variables collection. The variable might not exist in the correct scope.

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-28 : 07:51:21

I did some reading and discovered the parameter needs to be defined in Script Task. I also read that ‘Script Task uses .NET coding and resources’ (I haven’t done .NET coding yet). I did however dbl click on a task (data flow task) in the Control Flow designer window and I got nada (nothing). Is there another way to access the Script Task? Can I even use it just to control the flow of my pkg? Or is there another way to do this (to force an step failure)?

tm
Posting Yak Master

160 Posts

Posted - 2008-03-28 : 16:50:52

Based on your error .. Check the scope of your variable GotRecs?
Does it have the scope so the contraint can access it?

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-29 : 12:21:03

I managed to get the variable defined, I changed the name to StopHere. The problem is my setting are not corrent, the constrained step either runs all the time or never runs.

Here is what I did:
I created the variable (stopHere) It is within the scope, when I click 'TeST' in the Precedent Contraint dialog box I get the 'Expression validated successfully'. I then entered the following values in the Precedence Constraint:

Evaluation Operation: Expression
Value: N/A (ghosted)
Expression: @StopHere == true
Radio button: Logical AND

Now all I need to do is access the Script Task. How the blue blazes do I get my hands on the Script Task Editor? I tried dbl clicking every task in my pkg. All I get is their specific dialog box. From everything I've read I can enter a SQL statement that will link my variable (@StopHere) to my Precedence Contraint.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 12:38:07

just refer this to see how to access the script task editor.

http://www.mssqltips.com/tip.asp?tip=1443

waltraudweiland
Starting Member

1 Post

Posted - 2008-11-18 : 12:16:47

I wonder if you can not only stop the task from executing, but trigger the "OnError" event. Any suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources