Export to CSV from SSMS via PowerShell

On stackoverflow, someone asked how they can get a "real" csv export out of SSMS. Heck, that's easy, that's ummmmmm hmmmm, no. No, that's not going to work either. After some hemming and hawing and staying up too late, here's my initial pass on the solution

Export to CSV from Management Studio

1. Modify my Powershell export query to csv script to accept a parameter and save that to an accessible location. I used C:\tmp\ExportToCSV.ps1

   # 2011-05-25
   # Bill Fellows
   #
   # This PowerShell script is designed to demonstrate how to run an
   # *arbitrary* query against a database and dump to a csv. 
   #
   # Usage:  Save this file as C:\tmp\ExportToCSV.ps1
   # Start Powershell (Win-R, powershell)
   # Execute the script (C:\tmp\ExportToCSV.ps1)
   
   #
   # If the above fails due to
   # "cannot be loaded because the execution of scripts is disabled on this system"
   # run this command within Powershell
   # Set-ExecutionPolicy RemoteSigned
   # See also http://technet.microsoft.com/en-us/library/ee176949.aspx
   
   # http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
   $server = "localhost"
   $database = "master"
   $query = "SELECT 'highlight some text' AS [I am query] "
   
   if ($args.length -gt 0)
   {
       $query = $args[0]
   }
   
   # powershell raw/verbatim strings are ugly
   # Update this with the actual path where you want data dumped
   $extractFile = @"
   C:\test.csv
   "@
   
   # If you have to use users and passwords, my condolences
   $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
   $connectionString = [string]::Format($connectionTemplate, $server, $database)
   $connection = New-Object System.Data.SqlClient.SqlConnection
   $connection.ConnectionString = $connectionString
   
   $command = New-Object System.Data.SqlClient.SqlCommand
   $command.CommandText = $query
   $command.Connection = $connection
   
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter.SelectCommand = $command
   $DataSet = New-Object System.Data.DataSet
   $SqlAdapter.Fill($DataSet)
   $connection.Close()
   
   # dump the data to a csv
   # http://technet.microsoft.com/en-us/library/ee176825.aspx
   $DataSet.Tables[0] | Export-Csv $extractFile
   

2. Create an external tool that starts PowerShell
   
   1. Tools, External Tools...
   2. Add
   3. Title: ExporToCSV
   4. Command: %windir%\System32\WindowsPowerShell\V1.0\powershell.exe
   5. Arguments: -File C:\tmp\ExportToCSV.ps1 $(CurText)
   6. Initial Directory:
3. Highlight the query and under Tools, select ExportToCSV. You should now have a file at C:\test.csv

Limitations

1. Connection is not dynamic. I have a message out to another developer to see if they have any insight into how I can feed the current connection along and will update if I learn more.
2. SSMS will prompt you to save every open unsaved window. I typically have 30+ different windows open so you can imagine how painful that can be. The external tool will work just fine whether you save or cancel out of the save.