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.
