Export dynamically created table to excel/csv

Question

Export dynamically created table to excel/csv

Panks-913490

SSCrazy

Points: 2131
More actions
September 1, 2009 at 7:20 am

#135292

Hi,
I want to export a dynamically created table (with different number of columns in each run) to an excel/csv file through a SSIS package.
Can any one help in how to go about this?
Thanks
Pankaj

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

mcha7628 SSC Veteran Points: 265 More actions · Answer 1

If you are using an execute sql task to dynamically create the source table then you could use that same statement in an execute sql task to create a Excel spreadsheet.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

matthew_charron (9/2/2009)
If you are using an execute sql task to dynamically create the source table then you could use that same statement in an execute sql task to create a Excel spreadsheet.

Really? Can you provide an example?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

mcha7628 SSC Veteran Points: 265 More actions · Answer 3

For instance, if the table is being created dynamically then the user is probably using a query within a variable.

If the user used the same variable in another execute sql task with an excel connection they should be able to create an excel spreadsheet with that same query.

http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html

I just realized the only problem is the metadata.

It would create the file but there would be no connection from the source to the destination.

Does anybody know if you could do that programmatically? (VB, C#)

Panks-913490 SSCrazy Points: 2131 More actions · Answer 4

I would be using a dynamic SQL code in a stored procedure to create the table i want to export.

My problem to is dynamically create the connection , metadata information and the excel sheet to which data has to be exported.

Thanks

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

Do you have to do it in SSIS?

With Excel VBA OLEDB or ODBC it would be possible

to do it. If interested I can give some samples if you

provide some more details about what you want to

achieve as the end result.

/Gosta

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

Panks (9/1/2009)
Hi,
I want to export a dynamically created table (with different number of columns in each run) to an excel/csv file through a SSIS package.
Can any one help in how to go about this?
Thanks
Pankaj

Check CozyRoc Data Flow Task Plus with support for dynamic data flows.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

GSquared SSC Guru Points: 260824 More actions · Answer 7

If the dynamically created table is a persistent table, not a temp table, you could export it to csv pretty easily with bcp.

If you don't want to keep the table, you could create it in tempdb, populate it, export it, then drop it.

Creating csv files with bcp is pretty easy.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Panks-913490 SSCrazy Points: 2131 More actions · Answer 8

My table would not be a temp table it would be persistant.

Can you please explain how to use the bcp to export data to csv.

Thanks

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 9

Panks

"Can you please explain how to use the bcp to export data to csv."

Try Books on line or Google for:

bcp to export data to csv.

I got plenty 72 800 answers. You will find good examples there.

/Gosta