Using DTS to Query SQL Server and output to a CSV file.

[Mayoor]

I Have been asked to produce some reports from the data we have in an SQL Server Database.

The requirement is to use DTS to query the database and output the results into a CSV file

The requirement is for 4 different qreports, all of which are to be included in the same CSV file one after another, later to be opened up in Excel?

The CSV file must then be emailed to a list of recipients.

This package has to be ale to be run at regular monthly intervals and also be able to be executed from a command line by a technician who will also be able to define a month and a year to run the reports against.

Can someone advice on the best way to approach this

Thanks

Mayoor

 

[nigelrivett]

>> The requirement is to use DTS to query the database and output the results into a CSV file

Much easier without dts but ...

The requirement is for 4 different qreports, all of which are to be included in the same CSV file one after another

I would create these as different files then use the command line to concatenate

type c:\a.csv > output.csv
type c:\b.csv >> output.csv
type c:\c.csv >> output.csv

later to be opened up in Excel
Think excel will cope with different formats - try it and see - careful about size limits.

executed from a command line
can use dtsrun - give him a .bat file if necessary.

define a month and a year to run the reports against
Set a global variable from dtsrun then use that in an activex script at the start of the package to set the source queries for the exports.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.