Thx for the reply.

So, something like this, but using xp_cmdshell? 'sqlcmd -q "select col1,col2,col3 from table" -oc:\myfile.csv -h-1 -s","'

What about a retention period of these files? I guess I need a batch file?