Save daily SQL query result as Csv Into a daliy New File

Question

Save daily SQL query result as Csv Into a daliy New File

Kristofbraekmans

SSC Veteran

Points: 247
More actions
November 23, 2009 at 4:24 am

#213027

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,
but he doesn’t seem to make a new file every for every time.
Also the outputtfile should be automatically saved/exported as an Csv file.
These are my first steps with mssql server.
Im loving it, but now Im stuck since a few days.
Greetz Kristof
Ps: I was thinking it could be possible within task manager?
We also have visual studio and I can see the query result
there but I cant seem to get it automated.
--I’m not a programmer--

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

As you are creating a CSV file on a daily basis, this sounds like a job for an SSIS package. I'd like to recommend that you look into that to solve this issue. You can write the package such that it generats a new file each time it is run.

Steve Jones - SSC Editor SSC Guru Points: 738609 More actions · Answer 2

Lynn has a good idea. What you need is a programmatic task that will create a new file name each day. How are you doing this right now? What is the code that is running?

Kristofbraekmans SSC Veteran Points: 247 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Don't have anything for you with regard to SSIS, but I do have a suggestion regarding the query you posted:

SELECT

InteractionID,

StartRecord,

StopRecord,

FROM

dbo.tblRecording01

WHERE

dtRecordingGMTStartTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and

dtRecordingGMTStartTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1))

With regard to SSIS, two recommendations. One, start reading up on it in BOL (Books Online). Two, go to a local book store and check out the various books that are published on SSIS. I found the Wrox book (Professional SQL Server Integration Services 2005) very good when I was first working with SSIS, and still have it on my desk today.

Ken McKelvey SSCoach Points: 18938 More actions · Answer 5

If you are not using SSIS elsewhere, it may just be simpler to use PowerShell.

1. First create a view of your query. Something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
USE YourDB;
GO
CREATE VIEW dbo.YourView
AS
SELECT InteractionID, StartRecord, StopRecord
FROM dbo.tblRecording01
WHERE dtRecordingGMTStartTime >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)) and
    dtRecordingGMTStartTime < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1));
GO

2. Then create a SQL Server Agent Powershell step:

$YourCSVFileName = "<YourPath>\YourCSVFileName" + (Get-Date -Format "yyyyMMdd") + '.csv'

$SQLparams = @{
  'ServerInstance' = '.';
  'Database' = 'YourDB';
  'ErrorAction' = 'Stop';
  'Query' = 'SELECT * FROM dbo.YourView' }

$Exportparams = @{
  'Path' = $YourCSVFileName }

Invoke-Sqlcmd @SQLparams |
    Export-CSV @Exportparams -NoTypeInformation

This reply was modified 1 month ago by Ken McKelvey.
This reply was modified 1 month ago by Ken McKelvey.