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.