PowerShell Export SQL Agent Job

This brief post is in response to a comment on my post PowerShell export query to CSV. Octopusgrabbus, awesome handle, asked how one could launch this. If you were looking to make this into a SQL Agent job, then I'd simply create a new job, add a step of type PowerShell and then you can literally paste in the script I referenced in the previous article.

If you prefer a script approach, this should create a job named PowerShellExportTest and with a single step named PS test. When the job runs, the query in line 30 will run and generate a CSV to the path defined on line 35.

   1:  USE [msdb]

   2:  GO

   3:  DECLARE @ReturnCode INT

   4:  DECLARE @jobId BINARY(16)

   5:  EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'PowerShellExportTest', 

   6:          @enabled=1, 

   7:          @notify_level_eventlog=0, 

   8:          @notify_level_email=0, 

   9:          @notify_level_netsend=0, 

  10:          @notify_level_page=0, 

  11:          @delete_level=0, 

  12:          @description=N'No description available.', 

  13:          @category_name=N'[Uncategorized (Local)]', 

  14:          @owner_login_name=N'sa', @job_id = @jobId OUTPUT

  15:   

  16:   

  17:  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PS test', 

  18:          @step_id=1, 

  19:          @cmdexec_success_code=0, 

  20:          @on_success_action=1, 

  21:          @on_success_step_id=0, 

  22:          @on_fail_action=2, 

  23:          @on_fail_step_id=0, 

  24:          @retry_attempts=0, 

  25:          @retry_interval=0, 

  26:          @os_run_priority=0, @subsystem=N'PowerShell', 

  27:          @command=N'

  28:  $server = "localhost"

  29:  $database = "master"

  30:  $query = "SELECT D.* FROM sys.databases D"

  31:   

  32:  # powershell raw/verbatim strings are ugly

  33:  # Update this with the actual path where you want data dumped

  34:  $extractFile = @"

  35:  C:\test.csv

  36:  "@

  37:   

  38:  # If you have to use users and passwords, my condolences

  39:  $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"

  40:  $connectionString = [string]::Format($connectionTemplate, $server, $database)

  41:  $connection = New-Object System.Data.SqlClient.SqlConnection

  42:  $connection.ConnectionString = $connectionString

  43:   

  44:  $command = New-Object System.Data.SqlClient.SqlCommand

  45:  $command.CommandText = $query

  46:  $command.Connection = $connection

  47:   

  48:  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  49:  $SqlAdapter.SelectCommand = $command

  50:  $DataSet = New-Object System.Data.DataSet

  51:  $SqlAdapter.Fill($DataSet)

  52:  $connection.Close()

  53:   

  54:  # dump the data to a csv

  55:  # http://technet.microsoft.com/en-us/library/ee176825.aspx

  56:  $DataSet.Tables[0] | Export-Csv $extractFile', 

  57:          @database_name=N'master', 

  58:          @flags=0

  59:   

  60:  EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

  61:  EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'