Export dynamically created table to excel/csv

  • 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

  • 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.

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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#)

  • 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

  • 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

  • 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/

  • 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

  • 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

  • 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

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

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