x

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
more ▼

asked Apr 11, 2013 at 08:55 PM in Default

avatar image

tombiernacki
338 20 22 27

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 11, 2013 at 09:10 PM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1219
x476
x19

asked: Apr 11, 2013 at 08:55 PM

Seen: 1103 times

Last Updated: Apr 11, 2013 at 09:10 PM

Copyright 2017 Redgate Software. Privacy Policy