Post reply

Export output from stored proc to flat file.

  • June 13, 2009 at 3:32 am

    #384767

    I want to trap the output from my stored procedure call to a flat file. The data is output in csv and contains a file header row then multiple headers and details. See below.

    I,1,2,3,

    H,34,324,34,24,

    D,34,5,34

    D,43543563

    D,46,463,46

    H,43534

    D

    D

    Etc etc

    This data is derived by querying a database.

    Additionally I want to create a unique file name.

  • June 13, 2009 at 4:39 pm

    #1009183

    Hello,

    You can build an initial SSIS Package with the Data Import Export Wizard. Just go through the screens and use the “Write a query to specify data to transfer” option, and then in the subsequent Source Query Window specify an “Execute” of your SP.

    Towards the end of the Wizard you get the chance to save the process as an SSIS package.

    If that works, you can then edit the SSIS Package in Visual Studio to change the properties of the DestinationConnectionFlatFile.

    Add an Expression for the ConnectionString and set it to what you want e.g.

    "C:MyFolder\\SPOutput" + ((DT_WSTR,30) Day(GetDate())) + ".txt"

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

Viewing 2 posts - 1 through 1 (of 1 total)

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