My case is very simple but I can't resolve the issue using SQL Server 2008R2 BIDS.
I need to select out a list of managers from a table. Then use foreach loop container to loop through this list and feed to a stored procedure which takes 3 parameters (manager_name, date, interval) to generate a table, say saleperson's performance records under each manager.
I used execute SQL task to get the list of managers first. The variables are: FullResultSet (data type is object), RepDate (Date), ManagerID (nvarchar). I tried both single and full result set. None worked.
Next is the foreach loop. Use ADO enumerator, FullResultSet as source variable. Variable mapping both RepDate and ManagerID (with the correct order).
Inside the container, I use execute SQL task to execute the stored procedure as myStoreProcedure ?,?,?. Parameters mapped as RepDate, ManagerID and Interval (this is in variable list with a default value).
Issue: The foreach container looped the right number of times. However, the stored procedure never got executed. I didn't get any error message. So I don't know what went wrong? The project is due first thing tomorrow morning. If anybody could help, I'd much appreciated.