SQL Powershell/cmd script to get data from a table and export to .csv or flatfile

A sql script that connects to a sql server (server name = VENUS) select from multiple tables ( employee, department, (up to 30 tables)) then export the data as individual flat files/ .csv to a location d://extract. Once that script is ready i would use a windows task schedule or sql job to schedule the daily jobs


Would you consider using ssis ?

Something like:

'tableA','tableB' | %{$FileName = $$_; Invoke-Sqlcmd -ServerInstance '.' -Database 'Scratch' -Query "Select * From $($$FileName)" | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath "$($FileName).csv" }

(Those $$ should just be a single $ sign, but the forum is butchering my Powershell!)