This SP is failing in the database when we are running it manually but when we are calling this SP in the package our package is executed successfully

Recently we get a production issue. Our issue is that In the SSIS package using execute SQL task we are calling the SP. This SP is failing in the database when we are running it manually but when we are calling this SP in the package our package is executed successfully. Ideally, our package needs to be failed.

Let’s replicate this issue after that we will see the solution to fix this issue.

We are creating an SP that inserts the record in the table.

Table scrip

create table emp (id int identity(1,1) primary key, emp_name varchar(50), emp_add varchar(50), emp_mob varchar(10) ) --Log table create table Error_log ( log_id int identity(1,1) primary key, Error_msg varchar(max) )

Creating SP to insert the record.

CREATE PROCEDURE P_insert_emp                 (                 @emp_name varchar(50),     @emp_add varchar(50),     @emp_mob varchar(100)                 ) AS BEGIN declare @rt int; set @rt=1; begin try insert into emp (emp_name,emp_add,emp_mob)values (@emp_name,@emp_add,@emp_mob); end try begin catch set @rt=-1; insert into Error_log(error_msg) select ERROR_MESSAGE(); end catch; return @rt; END;

Now executing the Sp

declare @result int; execute @result= P_insert_emp 'Bagesh','Noida','88888XXXXX'; select @result;

 Record inserted successfully.  

Now calling this SP in the Package

Taking Execute SQL task and doing the configuration.  

Now running the package.  

Executed successfully.  See the records in the table. 

Now we are producing the error in the SP.

declare @result int;  execute @result= P_insert_emp 'Bagesh','Noida','88888XXXXX678905';

Passing the emp_mob value more than ten characters.  It is throwing an error which we have logged in the Error_log table.  

Now we are calling this SP with the same parameter in SSIS package.  

Now we are executing this package.  

Package executed successfully. Even though our SP is failing.

See the records in the table.  

We need to fail the package if the SP is failed in the DB.

To fail the package we will we the raiserror Function.

To use this function our SP must have either return statement or Output parameter then only we can fail our package.

In this demo SP we have return statement.

Writing below script in the Execute SQL task.

declare @result int,         @error_msg varchar(max);  execute @result= P_insert_emp 'Bagesh','Noida','88888XXXXX678905';  if (@result=-1) begin set @error_msg= (select Error_msg from Error_log where log_id=(select @@IDENTITY));  set @error_msg =' Error while inserting records in the Emp table. Error message :' + @error_msg;  raiserror(@error_msg , 16, 1); end;

  

Now we are running this package.

  

Package executed successfully.

See the error.

[Execute SQL Task] Error: Executing the query "declare @result int,         @error_msg varchar(ma..." failed with the following error: "Error while inserting records in the Emp table. Error message :String or binary data would be truncated in table 'Test_DB.dbo.emp', column 'emp_mob'. Truncated value: '88888XXXXX'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 This is the best way to use the raise error function when we are calling the SP or function in the Execute SQL task in the SSIS package.