CODEWITHSUNDEEP
ssis
Brian Bolton
Brian Bolton

Reputation: 3642

How can I manually fail a package in Integration Services?

I am running an Execute SQL Task statement in my SSIS package. The Execute SQL Task is running sql and checking that the tables have more than 1000 rows. If they have less than 1000 rows, I want to fail the package.

How do I force a fail inside of a SQL statement?

Upvotes: 20

Views: 40480

Answers (5)

Subhransu Panda
Subhransu Panda

Reputation: 77

  1. Create a user variable. Store the record count in that variable (User::Rcnt)
  2. Just use the send mail task and define the work flow.
  3. In precedent constraint (User::Rcnt<20)

Upvotes: 0

David Godfrey
David Godfrey

Reputation: 211

I like to force a failure by using a script task. It's really easy.

1) Add the script task

2) Change the line of auto-generated code from :

Dts.TaskResult = (int)ScriptResults.Success;

to 

Dts.TaskResult = (int)ScriptResults.Failure;

and there you go!

Upvotes: 21

Mahesh
Mahesh

Reputation: 3997

You can create a custom error message like this

EXEC sp_ADDMESSAGE 
        @msgnum=55555,
        @severity=1,
        @msgtext='Threshold condition failed. The package execution has been terminated.'

You can then call this error using Raiserror()

Raiserror (55555,20,-1) WITH LOG

You need to enter a number greater than 18 for severity (here 20). You need to have admin privileges for this. And dont forget WITH LOG

Upvotes: 4

Harnath
Harnath

Reputation:

You need to make property FailPackageOnFailure true..try to retrieve the property FailPackageOnFailure of the particular task and assign the value true. so that package will be failed.

Upvotes: 2

Harper Shelby
Harper Shelby

Reputation: 16583

AFAIK, tasks in SSIS fail on error. So if your Execute SQL Task has a statment like so in it:

declare @count int
select @count = select count(*) from my_table
if @count < 1000
begin
    raiserror('Too few rows in my_table',16,1)
end
else
begin
    -- Process your table here
end

You should get the results you want.

Upvotes: 22

Related Questions