asked
stored procedure to execute and output to excel
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.
14 People are following this question.
