Here’s a quick way to get data off on of your SQL Server table and dump it to a CSV file. Only 4 steps:
1. Make sure you are in SQLPS console, or have the community SQLPS (or SQLPSv2) module loaded ( ie. Import-Module SQLPS ) in either your PowerShell Console or ISE.
2. Have you query ready, then save it into a PowerShell variable:
$sql = “SELECT * FROM [AdventureWorks].[Production].[Location]”
3. Next one-liner will build the PowerShell object and exported to a *CSV file: (Execute in localhost only. Use -ServerInstance with the -database parameter if is needed t0 execute query)
Invoke-Sqlcmd -query $sql | Export-Csv -Path c:\temp\excelfile.csv -NoTypeInformation
4. Last line will open the file in Excel:
ii c:\temp\excelfile.csv
This surely beats going to SSIS for quick results!
For more information about the “Invoke-SQLcmd” use the help in PowerShell:
Help Invoke-SQLcmd -detailed