x

Execute SQL task - output recordset to Excel spreadsheet

I have my Execute SQL set up using a sp, my Foreach Loop Container is using an ADO Enumerator and I have it set to Rows in the first table and the variables are mapped. I'm not sure what I need to put in the data flow task to access the parsed fields. Do I need to use an ADO Net Source? I tried to use a SQL statement to access it - Select * from User:recordsetname but I'm not getting it. I'm looking for the objects and settings I need to get the variables to show up in my data flow task. Thanks
more ▼

asked Jan 04, 2012 at 07:35 AM in Default

Renee gravatar image

Renee
12 2 2 2

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

2 answers: sort voted first

If you want to populate an Excel sheet with the output from a stored procedure or query, you simply need to create the data flow.

You do not need a ForEach loop, unlike other programming languages where you have to iterate through the recordset. Simply create your source in the data flow, using the query/sp to extract the data and connect that with your Excel destination in the same data flow.
more ▼

answered Jan 04, 2012 at 10:41 AM

Martin Schoombee gravatar image

Martin Schoombee
221 2

Thank you. When I use the ADO.net source in the data flow I'm not seeing the option to use an sp, only to choose a table or a view. I can't drop my sp to a view b\c I'm using temp tables. Do I need to create an intermediate table to get around this or can I use my sp?
Jan 04, 2012 at 11:32 AM Renee

Don't use an ADO.NET source if you don't have to. It would be better to use an OLEDB or ODBC source. These data sources will allow you to use an sp or query.

Just remember to include a Data Conversion step to convert all your values to unicode, because Excel needs unicode data.
Jan 04, 2012 at 11:56 AM Martin Schoombee
Unless I'm missing a setting or an option, the only way I can see to execute a sp from there is to use a SQL command and exec the sp - even so, I can see the data if I click preview but it's giving me an error that no column info was returned - do I have to do a select statement after the exec and list all of the columns? If so, what do I reference in the FROM statement since the sp uses temp tables?
Jan 04, 2012 at 01:06 PM Renee

No, you are calling the sp correctly and you don't have to do a select afterwards (as long as the sp returns some data at least).

Set the "DelayValidation" property of that task to true. There are some known issues when you have a stored procedure or query and the output fields cannot be "validated" before the step is actually executed.
Jan 04, 2012 at 02:05 PM Martin Schoombee
Thanks for your help - that worked. I don't know that it made a difference (I don't want to mess with it since it's working now) but I also set the ValidateExternalMetadata to False.
Jan 05, 2012 at 09:14 AM Renee
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x939
x2

asked: Jan 04, 2012 at 07:35 AM

Seen: 1396 times

Last Updated: Jan 09, 2012 at 02:18 AM