[this intelligent life]

Blog Post

Posting to #Slack from SQL Agent

Mark Wojciechowicz • Feb 08, 2018

2018-02-08
Post to slack from SQL Agent? Why on earth would I want to do that? Because email sucks and slack is cool. Also, SQL agent doesn't shed to much light on what went wrong in an SSIS package execution. It just says go look at the logs, which is not very helpful.

What do we need?

  • Permission to execute powershell
  • Permission to read the log tables from ssisdb
  • A slack url to post to
Getting the url from slack
Once permissions are sorted out, go to https://my.slack.com/services/new/incoming-webhook/
On this page, choose the channel you want to post to and hit the Add Incoming Webhooks Integration button:

In the next page, select the url and save it to the clipboard

Prepare the powershell script
As shown in the script below, set the parameters for the ssis folder, project and package name. Optionally, you can specify the slack channel, though if you don't it'll just use what you specified when creating the url above. Paste in the url to $slackUrl.
Also note the url for $iconUrl is some page where I found an image of the icon for SSMS. This should be set to something more relevant for the job you are running.

The script will execute a query against ssisdb (you might need to adjust the connection information). The results are returned in the form of XML and this is formatted for readability. Lastly, we call invoke-webrequest and post our message to slack:

 #set all these parameters
$ssisFolder = ""
$ssisProject = ""
$ssisPackage = ".dtsx"
$slackChannel = "" #optional
$iconUrl = "https://d2.alternativeto.net/dist/icons/sql-server-management-studio_60533.png?width=200&height=200&mode=crop&upscale=false"
$slackUrl = ""

function Format-XML ($xml, $indent=2) 
{ 
    $StringWriter = New-Object System.IO.StringWriter 
    $XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter 
    $xmlWriter.Formatting = “indented” 
    $xmlWriter.Indentation = $Indent 
    $xml.WriteContentTo($XmlWriter) 
    $XmlWriter.Flush() 
    $StringWriter.Flush() 
    Write-Output $StringWriter.ToString() 
}

$cmd = "Declare @xml as xml =
	(SELECT 
		message_time
		, o.operation_id
		, package_name
		, message_source_name
		, execution_path
		, message
	FROM catalog.event_messages o
	WHERE o.operation_id =  (SELECT top 1 e.execution_id FROM catalog.executions e
					where folder_name = '$($ssisFolder)'
					and project_name = '$($ssisProject)'
					and package_name = '$($ssisPackage)'
					and status = 4
					order by e.execution_id desc)
	and message_type = 120
                order by o.event_message_id
	For xml path, Root('Messages')
	) 

SELECT @xml as JobResult"

$jobResult = Invoke-Sqlcmd -ServerInstance . -Database SSISDB -Query $cmd
[string]$errorMessages = Format-XML ([xml]$jobResult.JobResult)

$payload = @{
            "channel" = "$($slackChannel)"
            "username" = "SQL Server"
            "icon_url" = "$($iconUrl)"
            "text" = "The following errors occurred while running the pay etl:`````` $errorMessages ``````"}


Invoke-WebRequest `
    -Body (ConvertTo-Json -Compress -InputObject $payload) `
    -Method Post `
    -UseBasicParsing `
    -Uri $slackUrl | out-Null 

Add the SQL Agent Step
Next, add a step to SQL Agent. Set the type to "powershell" and paste in the script that we prepared above. Go to advanced and set the step to report failure on success so we have an accurate history of when the SQL Agent job actually does fail. Also, set prior steps to go to this step on failure.

For testing, we can just execute the job from the slack step. However, it's helpful to have a prior package failure or it will have nothing to report. This is what it looks like on slack:


Share by: