[b]Problem Exporting Query Results to a CSV File[/b]

[Nassy]

Hi everybody,

I have a fairly complex query which I wish to export to a CSV file on a daily basis and then e-mail a recipient.

The Problem

First of all I have tried to write the results of the query to a .CSV file using a BCP command but SQL complains that the query is too long to be used as a BCP argument

Secondly I have tried to use DTS using the data pump task using a text file as a destination. However...

The text file task forces you to select a file that is already created. This would be fine except that each time I run the task I want the results in the CSV file to overwrite the old results from the previous run. How do I do this? DTS doesn't give you the option of overwriting text file results in the data pump task.

I have had similar problems before with DTS - DTS is one of the parts of SQL Server I find fairly awkward and don't feel I have mastered. I would happy to do this some other way too if that is best. Any help on this would be hugely appreciated.



Nassy

 

[JayKusch]

I beleive that by default the DTS export of your query to a text file WILL over write the contents of what is already in the fire and you worry of ... "I want the results in the CSV file to overwrite the old results from the previous run." is accomplished.

As a test, create a simple DTS export of the Region table in the Northwind database if you have it. IF not, create a 2 column table of your chioce and add 4 records. Create the DTS package to export the test table to a text file. Run it and see that the 4 records are now in the text file.

Then run it again. 10 to 1 you will not have 8 records in the text file but the 4 proving that the export over-writes the file. This will work the same for your complex query ... same export mechanism is being used.

Enjoy!

Thanks

J. Kusch

 

[ecobb]

JayKusch is right, the DTS package WILL overwrite the existing csv file on export. I recently set up a DTS package exactly like this. I have 2 complex queries exporting info out to 2 csv files each night, and every night the old csv files get overwritten.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[Nassy]

Thanks everyone for the quick replies,

I have taken on board the advice and am trying to download query results to a .CSV file as a test using a simple data pump task.

However there is one small problem. I get an invalid pointer error each time I try the download. It is maddening - I don't have a clue where I am going wrong.

Here are the steps I am following:


[ul]
[li]Create an Excel file of type .CSV called purchasers[/li]
[li]Create a DTS package with a OLE DB Server task and Text File (Destination) Task[/li]
[li]Use a Data Pump transformation task to connect the items[/li]
[li]Use the query as the source and set the transformations[/li]
[/ul]

The file I am trying to write to is already a comma separated file but in the Options dialog I make sure to set the following:


FileType: ANSI
Row Delimiter: {CR}{LF}
Column Delimiter: Comma
Text Qualified : Double Quote


I have played around with other options but I still get the pointer error. Any clues on why I get this?

Thanks again

Nassy

 

[JayKusch]

Are we going to a text file or an Excel spreadsheet???? Looks like you are combining the two in some sort of fashion.


Thanks

J. Kusch

 

[Nassy]

I can download to a text file created in notepad fine and can just make sure each column is separated by commas. But I can't download to an Excel .csv file. I would prefer the results to be in Excel. Sorry if this was not clear earlier.

Thanks

Nassy

 

[john76]

Something else to try...

If bcp doesn't like the length of your query (understandable, many queries tend to be longer than the "SELECT 'Hello World'" variety), then you could use an Execute SQL task to run your query, using the INTO clause to write the results to a temp table, then bcp the results out from that temp table.

Runs faster than a Transform Data task also, but you do have to consider that bcp does not include field titles. If you need them, you will need to write the query so that the field names are the first row of data, then convert all numeric values to varchar.

SELECT field1,
       field2,
       field3

  INTO tempdb..output_table

  FROM ( SELECT field1 = [COLOR=red]'Field_1'[/color],
                field2 = [COLOR=red]'Field_2'[/color],
                field3 = [COLOR=red]'Field_3'[/color]
           UNION ALL
         SELECT field1 = [COLOR=maroon]CONVERT[/color]([COLOR=blue]varchar[/color](1000), Field_1),
                field2 = [COLOR=maroon]CONVERT[/color]([COLOR=blue]varchar[/color](1000), Field_2),
                field3 = [COLOR=maroon]CONVERT[/color]([COLOR=blue]varchar[/color](1000), Field_3)
           FROM myData
       ) fieldnames

(I'm not actually sitting in front of my terminal that has SQL Server installed, so I don't know if the query above is "correct" but you should get the idea.)

Hope this helps,
John

 

[dslarve]

Another option (if you're concerned with the length), save the query as a stored procedure, and call the stored procedure in the dts- one line of code. Also, if you want to archive the doc/rename it/ whatever, you can add a step to the dts package to execute program. For the program, write a simple batch file that does a copy, move, and rename of the file. Here's the batch file I wrote to accomplish that, it concates the name with timestamp to keep the filename unique.

@echo off

for /F "tokens=2,3,4 delims=/ " %%i in ("%date%") do set d=%%k%%i%%j
for /F "tokens=1,2,3 delims=:. " %%i in ("%time%") do set t=%%i%%j%%k


cd C:\FOLDER1
copy FILE.txt C:\ARCHIVEcd C:\ARCHIVErename FILE.txt FILE_%d%_%t%.txt