Need to run few sql scripts and store the results in a CSV file

Question

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

This is pretty easy in SSIS:

you loop over the files with a for each loop, pick up the query and use bcp with the queryout option to write it to a .csv file.

Working with the bcp Command-line Utility[/url]

Note: csv files are flat files, they can have only one "sheet".

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Use SSIS for this... It will be easy 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

kapil_kk (9/13/2013)
Use SSIS for this... It will be easy 🙂

Not necessarily. If each query returns different metadata, you can't use a dataflow.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 4

Thanks for your reply......

I can't place it in SSIS as we don't have SSIS in all the environments . I am trying with sqlcmd which is little bit easy to implement..

This loops and executed all the sqlserver scripts in a location:

for %%G in (*.sql) do sqlcmd /S servername /d databasename -U username -P password -i"%%G"

This will insert data into CSV.

sqlcmd -S klingon -d stardb -i C:\testscript.sql -o C:\testresults.csv -h -1 -s ","

I want to combine both of these to get required output... I stuck up hrere as of now.

Thanks,

Vamsi

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

If you need to do something programmatic and you can't use SSIS, I'd suggest looking into PowerShell.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt