Executing a List of Stored Procedures – Some of Which are Parameterised

SSIS and Import/Export
1

Hi
I currently have a list of stored procedures in a table that needs to be run via SSIS and placed into a staging table.

ID StoredProcedure IsEnabled
1 SprocA 1
2 SprocB 1
3 SprocC 1
4 SprocD 1

I was planning on running these through a ForEach loop using an ADO data set. The OLE DB Source will use an expression which will be populated by the stored procedure name at run-time.

"EXEC dbo."+ @[User::StoredProcedure]”

However I have found that some of the procedure will be parameter based and some not. I just wanted to check if it was possible to run all these procedures together in the same ForEach loop, and what I would have to do to the expression to handle both types of procedures.

I was thinking of adding a new field to the stored procedure table which will indicate if it is parameter based and then running two ForEach Loops, one for the procedures with parameters, and the other for the ones without.

ID StoredProcedure IsEnabled ParameterBased
1 SprocA 1 1
2 SprocB 1 1
3 SprocC 1 0
4 SprocD 1 0

Anyone have any ideas or better solutions on how I can achieve this?

Thanks in advance.

2

Call a "wrapper procedure" with the name of the SProc and all the possible parameters.

The Wrapper Procedure can check which parameters the SProc requires, and use dynamic SQL to execute it, with its actual parameters.

This way, provided all necessary parameters are passed to the Wrapper Procedure, and of the Child Procedures can (now, or in the future) require none, some or all of the parameters
I don;t know if that would be an option with SSIS though ...