Execute multiple stored procedures with one input parameter SSIS

0
votes

I am trying to execute multiple stored procedures using only one input parameter in SSIS using Execute SQL Task but I keep getting this error:

[Execute SQL Task] Error: Executing the query "EXEC sample_stored_proc1..." failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here is a sample code inside SQL TASK for calling the stored procedures:

EXEC sample_stored_proc1 ?;
EXEC sample_stored_proc2 ?;
EXEC sample_stored_proc3 ?;

enter image description here

NOTES

  1. I tried the code with only 1 stored procedure and it works but adding 1 or more stored procedures is giving me the above error.
  2. using OLE DB connection
2
sql-serverssis
Believe it or not you have three parameters in your input SQL. You need to repeat your parameter mapping three times. But having a wrapper proc is a better idea anyway – Nick.McDermaid

2 Answers

0
votes

I found one solution, I created a new Stored Procedure with an input parameter and put this in EXECUTE SQL TASK instead. this will be used to execute all the stored procedures above.

ALTER PROCEDURE [dbo].[xp_EXEC_ALL_sample_SPs] @Start_Date as DATE
AS
BEGIN
        EXECUTE sample_stored_proc1 @Start_Date;
        EXECUTE sample_stored_proc2 @Start_Date;
        EXECUTE sample_stored_proc3 @Start_Date;

END

Please let me know if there are better solution

0
votes

when you execute a stored procedure it returns a execution status, so when you ran three procedure it returned multiple result sets, you can simply ignore the result set returned by

DECLARE @result int;
DECLARE @start_date DATE = ?;
EXEC @result = sample_stored_proc1 @start_date;
EXEC @result = sample_stored_proc2 @start_date;
EXEC @result = sample_stored_proc3 @start_date;

doing so will suppress multiple result sets from the query