Results of stored procedure into Excel

• ByronOne

  SSCarpal Tunnel

  Points: 4656

  More actions

  August 5, 2016 at 1:36 pm

  #400529

  Hi

  I have a stored procedure called dbo.spr_RegionalSales

  I use an Exexute SQL transformation to execute this procedure but I don't know how to save the results of the output into an Excel file on the file directory (the last bit of the procedure is a simple select statement from a cte table called RegionSales).

  I know if I changed the procedure so that it inserted the final output into a permanent table I could just use a data flow task from that table to the file directory; but I wondered whether there was a way of avoiding this?

  Thanks in advance.

  BO

• tim.ffitch 25252

  SSCrazy

  Points: 2055

  More actions

  August 26, 2016 at 9:29 am

  #1897848

  If you data flow is complaining because it can' determine the Meta Data from the stored procedure then you could use:

  exec p_MyProcedureName WITH RESULT SETS ((myID INT NOT NULL, myField1 VARCHAR(2), myField2 DECIMAL(15,2) etc...))

  You change this for your own procedure name and field definitions.