How can I manually fail a package in Integration S

Question

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?

Answer 1

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)

Answer 2

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.

Answer 3

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!

Answer 4

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

Answer 5

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.