A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Tuesday, December 18, 2012

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)'

No comments: