locked
Export daily sql query result to csv in daily new file RRS feed

  • Question

  • Hi all,

    I have a doubt, how to create a schedule for a query to run every day,
    the result should be saved every time the query runs, into a new file

    For the moment I use SQL Server Agent to do this daily,
    with a jobtask and my query inserted. The output has no extension
    he doesn’t seem to make a new file every time, just replaces it.

    Also the outputtfile should be automatically saved/exported as an Csv file.If thats possible...
    These are my first steps with mssql server.
    Im loving it, but now Im stuck since a few days.

    Greetz Kristof 

     

    Monday, November 23, 2009 2:30 PM

Answers

  • I would move this over to the other forum for the best/fastest help (SSIS or TSQL).  You could create a simple SSIS package that just has a data transformation task that runs the query and rights to CSV.  Your Control Flow would only have one task and your data flow with have source and destination only.  Very simple package to right.  I don't have SSIS on this machine but you should be able to name the files with a date, or easily build an SSIS expression for the file name (but I am pretty sure the designer has an increment option).

    Again -- try your answer to me in one of the other forums for those experts to reply something different than mine.
    • Marked as answer by Jerry Nee Friday, December 4, 2009 8:09 AM
    Monday, November 23, 2009 3:52 PM

All replies

  • Are you using Reporting Services?  Is this a report?

    If it is then you can schedule the report to run, the type of output file and to keep each version in a certain folder with an auto-incrementing name.
    Monday, November 23, 2009 2:35 PM
  •  
    For the moment I use SQL server agent as a job with Transact-SQL script (T-SQL)

    The output file is daily generated @ 00:00
    He replaces the content in the file every day.
    The result has no extension.


    example:
    'SELECT InteractionID, StartRecord, StopRecord,
    FROM dbo.tblRecording01
    WHERE (DATEADD(day, DATEDIFF(day, 0, dtRecordingGMTStartTime), 0) = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))



    Every help is welcome, even in SSIS


    Thanks Kristof
    Belgium
    Monday, November 23, 2009 2:58 PM
  • I would move this over to the other forum for the best/fastest help (SSIS or TSQL).  You could create a simple SSIS package that just has a data transformation task that runs the query and rights to CSV.  Your Control Flow would only have one task and your data flow with have source and destination only.  Very simple package to right.  I don't have SSIS on this machine but you should be able to name the files with a date, or easily build an SSIS expression for the file name (but I am pretty sure the designer has an increment option).

    Again -- try your answer to me in one of the other forums for those experts to reply something different than mine.
    • Marked as answer by Jerry Nee Friday, December 4, 2009 8:09 AM
    Monday, November 23, 2009 3:52 PM