I am working on a project and got stuck on looking for the best solution... I have a stored procedure with 3 parameters. The stored procedure returns the Campus Location, UserName, and Date that they were created. The parameters are @campus, @date, and @id. The @date stands for the date to run the SP. The @id stands for if its 1 then run the SP for full month or if its 0 then run it for previous day. **My Goal is to create a SSIS package and execute the Stored procedure have it run for 10 Campuses and output the results to a csv file. Each campus would be each own cvs file so 10 in total. How do I go about this??? I am stuck , any help would be appreciated...** I have a table with all the campus names. How can I run the SP for all the Campuses based on the @Campus parameter? the SP looks like this.... EXEC dbo.StoreProcName` 7, '4/11/2013',1 @Campus int, @date smalldatetime, @id int as set nocount on; If @id < 1 select campus ,displayname, datecreated from Tablename where datecreated >@date and datecreated < @date +1 and Table.dbo.Campus = @campus If @id >=1 select campus ,displayname, datecreated from Tablename where datecreated >@date and datecreated < @date +1 and Table.dbo.Campus = @campus
I'm not a regular user of SSIS but I would suggest that using a ForEach Loop Enumerator would be where you need to look.
http://technet.microsoft.com/en-us/library/ms141724.aspx This will let you apply logic relevant to each execution of the procedure. That way, with a variable that you manipulate to hold the value of the Campus that you are working with, you can export the data to the csv's as you require.