In some project we need to execution stored procedure in SSIS Execute SQL Task and want to capture the stored procedure’s return value. Can we do this in SSIS Execute SQL Task? How can implement it in SSIS?
The answer is yes. The Execute SQL Task configuration is depend on your stored procedure parameter setting and database connection type. For output parameter and stored procedure return value, the SSIS parameter config is different For OLE DB connection and ADO.NET connection config are different as well. In below example, I am using OLE DB connection.
Firstly, we need to create one stored procedure with both output parameter and return value:
create procedure sp_check_import_status @client_code varchar(10),@currenty_day varchar(8),
@output int output
as
begin
declare @result int
select @result = count(1) from transfer_history
where client_code=@client_code and day_transactions >= @currenty_day
set @output = @result
if @result = 1
begin
return @result
end
return 0
end
Secondly, need to config SSIS Execute SQL Task
Double click Execute SQL Task to open task editor. In the editor window, set ResultSet to None and SQLSourceType to Direct Input, input following statement to SQLStatement
exec ? = sp_import_oan_check_import_status ?,?,? output
Switch to the Parameter Mapping window, click Add to add the variables/parameters created in SSIS project and map the parameters with the stored procedure result as follows:
Note that the ParameterName values are numeric. You need to set the name starting from 0 according to the parameters occurrence sequence in the SQLStatement.