Octopus.Script exported 2015-04-06 by chrisgelhaus belongs to ‘SQL Server’ category.
Execute a SQL Agent Job and wait for results.
Parameters
When steps based on the template are included in a project’s deployment process, the parameters below can be set.
Connection String
ConnectionString = Server=;Database=msdb;Integrated Security=True;
Connection string for the SQL connection. Example:
Server=.\SQLExpress;Database=OctoFX;Integrated Security=True; Bind to a variable to provide different values for different environments.
Job Name
JobName =
SQL Agent job to run. Can be bound to a variable split. Text output by the PRINT statement in SQL will be logged to the deployment log.
Timeout Value
Timeout = 120
The maximum length of time in 5 second intervals to wait for job completion. The default value is 600 seconds (120 intervals x 5s = 600s)
Last Job Step
Step = 1
The number of the last step to run for the given job. The default value is 1
Script body
Steps based on this template will execute the following PowerShell script.
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $OctopusParameters['ConnectionString']
Register-ObjectEvent -inputobject $connection -eventname InfoMessage -action {
write-host $event.SourceEventArgs
} | Out-Null
function Run-SqlAgentJob($jobname,$timeout,$stepid) {
$sqlstring = @"
SET NOCOUNT ON
--Declaration
DECLARE @jobtorun VARCHAR(MAX) = '<JobName>'
DECLARE @jobid VARCHAR(50) = ''
DECLARE @previousid INT
DECLARE @previous_status INT
declare @newid INT
DECLARE @runstatus INT
CREATE TABLE #results
(
instance_id INT,
job_id varchar(255),
job_name VARCHAR(255),
step_id INT,
step_name VARCHAR(255),
sql_message_id INT,
sql_severity INT,
message VARCHAR(MAX),
run_status INT,
run_date INT,
run_time INT,
run_duration INT,
operator_emailed VARCHAR(255),
operator_netsent VARCHAR(255),
operator_paged VARCHAR(255),
retries_attempted INT,
server sysname
)
--Get Job ID
SELECT @jobid = job_id FROM msdb.dbo.sysjobs where name = @jobtorun
IF @jobid = ''
BEGIN
RAISERROR ('Job Name Not Found.', -- Message text.
16, -- Severity.
1 -- State.
);
RETURN
END
--Store previous job history
INSERT INTO #results
EXEC sp_help_jobhistory @job_id = @jobid, @mode = 'full', @step_id = <StepId>
SELECT @previousid = t.instance_id, @previous_status = t.run_status FROM (SELECT TOP 1 instance_id, run_status FROM #results ORDER BY instance_id DESC) t
PRINT 'Previous job ID: ' + CAST(@previousid AS VARCHAR(5)) + ' Run Status:' + CAST(@previous_status AS VARCHAR(5))
SET @newid = @previousid
--Start SQL Agent Job
EXEC msdb.dbo.sp_start_job @jobtorun
--Loop for x seconds or until jobhistory has been updated with a new record
DECLARE @loopct INT = 1
WHILE (@newid = @previousid) and (@loopct < <Timeout>)
BEGIN
TRUNCATE TABLE #results
INSERT INTO #results
EXEC sp_help_jobhistory @job_id = @jobid, @mode = 'full', @step_id = <StepId>
SELECT @newid = instance_id, @runstatus = run_status FROM #results WHERE instance_id = (SELECT MAX(instance_id) FROM #results)
PRINT 'Poll ' + CAST(@loopct AS VARCHAR(5)) + ' Time: ' + CONVERT(VARCHAR(8), GETDATE(), 108)
SET @loopct += 1
WAITFOR DELAY '00:00:05'
END
IF @newid = @previousid
RAISERROR ('Job did not complete in time.', -- Message text.
16, -- Severity.
1 -- State.
);
IF @runstatus <> 1
RAISERROR ('Job did not complete successfully.', -- Message text.
16, -- Severity.
1 -- State.
);
PRINT ''
PRINT 'Time: ' + CONVERT(VARCHAR(8), GETDATE(), 108) + ' New Job ID:' + CAST(@newid AS VARCHAR(5)) + ' Run Status:' + CAST(@runstatus AS VARCHAR(5))
--Cleanup
DROP TABLE #results
"@
$jobname = $jobname -replace "'", "''"
$sqlstring = $sqlstring -replace "<JobName>", $jobname
$sqlstring = $sqlstring -replace "<Timeout>", $timeout
$sqlstring = $sqlstring -replace "<StepId>", $stepid
#Debug Code
#Write-Host $sqlstring
$command = $connection.CreateCommand()
$command.CommandText = $sqlstring
$command.CommandTimeout = 0
$command.ExecuteNonQuery() | Out-Null
}
Write-Host "Connecting"
try {
$connection.Open()
Write-Host "Running SQL Agent Job"
Run-SqlAgentJob -jobname $OctopusParameters['JobName'] -timeout $OctopusParameters['Timeout'] -step $OctopusParameters['Step']
}
finally {
Write-Host "Closing connection"
$connection.Dispose()
}
Provided under the Apache License version 2.0.
To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.
{
"Id": "7bf5fc6b-9174-48ab-8da5-abf0eeef297a",
"Name": "SQL - Execute SQL Agent Job",
"Description": "Execute a SQL Agent Job and wait for results.",
"Version": 14,
"ExportedAt": "2015-04-06T20:01:54.552+00:00",
"ActionType": "Octopus.Script",
"Author": "chrisgelhaus",
"Parameters": [
{
"Name": "ConnectionString",
"Label": "Connection String",
"HelpText": "Connection string for the SQL connection. Example:\n\nServer=.\\SQLExpress;Database=OctoFX;Integrated Security=True;\nBind to a variable to provide different values for different environments.",
"DefaultValue": "Server=;Database=msdb;Integrated Security=True;",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Name": "JobName",
"Label": "Job Name",
"HelpText": "SQL Agent job to run. Can be bound to a variable split. \nText output by the PRINT statement in SQL will be logged to the deployment log.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Name": "Timeout",
"Label": "Timeout Value",
"HelpText": "The maximum length of time in 5 second intervals to wait for job completion. \nThe default value is 600 seconds (120 intervals x 5s = 600s)",
"DefaultValue": "120",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Name": "Step",
"Label": "Last Job Step",
"HelpText": "The number of the last step to run for the given job. \nThe default value is 1",
"DefaultValue": 1,
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
}
],
"Properties": {
"Octopus.Action.Script.ScriptBody": "$connection = New-Object System.Data.SqlClient.SqlConnection\n$connection.ConnectionString = $OctopusParameters['ConnectionString']\nRegister-ObjectEvent -inputobject $connection -eventname InfoMessage -action {\n write-host $event.SourceEventArgs\n} | Out-Null\n\nfunction Run-SqlAgentJob($jobname,$timeout,$stepid) {\n\t$sqlstring = @\"\n\t\tSET NOCOUNT ON\n\n\t\t--Declaration\n\t\tDECLARE @jobtorun VARCHAR(MAX) = '<JobName>'\n\t\tDECLARE @jobid\tVARCHAR(50) = ''\n\t\tDECLARE @previousid INT\n DECLARE @previous_status INT \n\t\tdeclare @newid\tINT\n\t\tDECLARE @runstatus\tINT\n\n\t\tCREATE TABLE #results\n\t\t(\n\t\t\tinstance_id INT,\n\t\t\tjob_id\tvarchar(255),\n\t\t\tjob_name VARCHAR(255),\n\t\t\tstep_id\tINT,\n\t\t\tstep_name VARCHAR(255),\n\t\t\tsql_message_id INT,\n\t\t\tsql_severity INT,\n\t\t\tmessage VARCHAR(MAX),\n\t\t\trun_status INT,\n\t\t\trun_date INT,\n\t\t\trun_time INT,\n\t\t\trun_duration INT,\n\t\t\toperator_emailed VARCHAR(255),\n\t\t\toperator_netsent VARCHAR(255),\n\t\t\toperator_paged VARCHAR(255),\n\t\t\tretries_attempted INT,\n\t\t\tserver sysname\n\t\t)\n\n\t\t--Get Job ID\n\t\tSELECT @jobid = job_id FROM msdb.dbo.sysjobs where name = @jobtorun\n\t\tIF @jobid = ''\n BEGIN \n \tRAISERROR ('Job Name Not Found.', -- Message text.\n \t\t\t\t16, -- Severity.\n \t\t\t\t1 -- State.\n \t\t\t\t);\n \tRETURN\n END\n\n\t\t--Store previous job history\n\t\tINSERT INTO #results\n\t\tEXEC sp_help_jobhistory @job_id = @jobid, @mode = 'full', @step_id = <StepId>\n SELECT @previousid = t.instance_id, @previous_status = t.run_status FROM (SELECT TOP 1 instance_id, run_status FROM #results ORDER BY instance_id DESC) t\n PRINT 'Previous job ID: ' + CAST(@previousid AS VARCHAR(5)) + '\t\tRun Status:' + CAST(@previous_status AS VARCHAR(5))\n\t\tSET @newid = @previousid\n\n\t\t--Start SQL Agent Job\n\t\tEXEC msdb.dbo.sp_start_job @jobtorun\n\n\t\t--Loop for x seconds or until jobhistory has been updated with a new record\n\t\tDECLARE @loopct\tINT = 1\n\t\tWHILE (@newid = @previousid) and (@loopct < <Timeout>)\n\t\tBEGIN\n\t\t\tTRUNCATE TABLE #results\n\t\t\tINSERT INTO #results\n\t\t\t\tEXEC sp_help_jobhistory @job_id = @jobid, @mode = 'full', @step_id = <StepId>\n\n\t\t\tSELECT @newid = instance_id, @runstatus = run_status FROM #results WHERE instance_id = (SELECT MAX(instance_id) FROM #results)\n\n\t\t\tPRINT 'Poll ' + CAST(@loopct AS VARCHAR(5)) + '\t\tTime: ' + CONVERT(VARCHAR(8), GETDATE(), 108) \n\n\t\t\tSET @loopct += 1\n\t\t\tWAITFOR DELAY '00:00:05'\n\t\tEND\n\n\t\tIF @newid = @previousid\n\t\t\tRAISERROR ('Job did not complete in time.', -- Message text.\n\t\t\t\t\t 16, -- Severity.\n\t\t\t\t\t 1 -- State.\n\t\t\t\t\t );\n\t\tIF @runstatus <> 1\n\t\t\tRAISERROR ('Job did not complete successfully.', -- Message text.\n\t\t\t\t\t 16, -- Severity.\n\t\t\t\t\t 1 -- State.\n\t\t\t\t\t );\n\n\t\tPRINT ''\n\t\tPRINT 'Time: ' + CONVERT(VARCHAR(8), GETDATE(), 108) + '\tNew Job ID:' + CAST(@newid AS VARCHAR(5)) + '\t\tRun Status:' + CAST(@runstatus AS VARCHAR(5))\n\n\t\t--Cleanup\n\t\tDROP TABLE #results\n\"@\n\n $jobname = $jobname -replace \"'\", \"''\"\n\t$sqlstring = $sqlstring -replace \"<JobName>\", $jobname\n\t$sqlstring = $sqlstring -replace \"<Timeout>\", $timeout\n\t$sqlstring = $sqlstring -replace \"<StepId>\", $stepid\n\t\n\t#Debug Code\n\t#Write-Host $sqlstring\n\t\n\t$command = $connection.CreateCommand()\n\t$command.CommandText = $sqlstring\n\t$command.CommandTimeout = 0\n\t$command.ExecuteNonQuery() | Out-Null\n}\n\nWrite-Host \"Connecting\"\ntry {\n $connection.Open()\n\n Write-Host \"Running SQL Agent Job\"\n Run-SqlAgentJob -jobname $OctopusParameters['JobName'] -timeout $OctopusParameters['Timeout'] -step $OctopusParameters['Step']\n}\nfinally {\n Write-Host \"Closing connection\"\n $connection.Dispose()\n}\n",
"Octopus.Action.Script.Syntax": "PowerShell"
},
"Category": "SQL Server",
"HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/sql-execute-sql-agent-job.json",
"Website": "/step-templates/7bf5fc6b-9174-48ab-8da5-abf0eeef297a",
"Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAD9QTFRFlZ+r3DAr6p+dy8/V4G9t////5efp9M7NrLS+wCYm8/T1vcPK1tnd10xK+fn6/PLyUU5O+eXk3+Hk7O3u7/DxS2XoPwAADb9JREFUeNrsnYl6nbgOgMEYDHghGN7/Wa8k70B6s3AOZD5o2umcSaf+0S4bUbX/kat6QB6QB+QBeUAekAfkAXlAHpAH5AF5QB6QB+QBeUAekAckXMv4XwBZVCPVnwcZlZSNXRrzp0HGTkqplrY1zfKHQboGMZwoGvVXQUbVy152QaPUu3XrJJCl6Xsp1/SBfbdunQJiZd/3zVqqmfprIEb1iLHRpLF5s279FsQ0iCH3etQ03R8CQYyq74/MwTbN3wGxQFGRRJTaJiVL815z/wXIIiviQEunq2lsNyZhvdfcfw6iCMPavl9H20jkgV8gP1F2NRRJmvEvgIA4gAS0B8xkpexEYWB3F0ijAyOxfwAkcsBvHQk53QWW71HwGm8PIhJHazIS98HYdUqBar1TJD8EYQOABGNe+w0J0dj3iuSHIOMw6PRHOyDpdhggE2XvDmLYAChsDh4MSPI1g92DWkGaosbbey0kARbOyFCaTCYgDemioQWp3D+O9EO4NGNCRpIFMKQzjlG9TyS/iOwoE64jjeaVwICOzjeoGfgue38QshPRMV57lhpVjbNemZTMK7X+gaQRSRgQzaz2JDX9CjRiDvWV+gMgRniSltWMMV0TSo1fcIEjEAKUa7k/CDiomkjaeeAU8JEmoRAOuoLp/hWidTJp9RBiipkF07our9fj/Lpmn51MeM2TnAx5gnp/cRZj6P2aD6BdWoBu1QUeiESwWoCu8a10OBfzHUFaATIxoFssfjIxUKbZiJobkg/ibFSNny2aM/pa4Lt0y4eoWwJkQP9S11NQNoOmw18Ic0qDDsIIg59TiC517aTDa5a7OBDPLDjRBMemmbgTCIhjEINbNVpHLXzozzxAhI4mg9ETv7i4DwhYiHa6JfA2T9F6dPltaDwgBQifwgG5ZOAMlpNAZlrShEpW8ykG/mgkCaMmX40LXwX3uUBR21wLgoYxoMOtc22agpJlGBM5AYF5pcFUwOkXXr8Ty2n7IxrWgze4sIo6WrvD4LNx6pc8QDtzHVA0uwGIcJ6otO4IQhahfZLCtqYjYiUwsOlqEMMp8S31w4MIHrUKv1PvnZlhsUJjF4NAWHQ5PCRUIoGA5XutEpMJsquPFjvzX6GcB2I0Ybg45wWDpi/Iz7K07QPiOfZQEwtls7gShCL6kGe6U4tBg8Bmk7syfSjRpF0glOVCEDT3Mp0KQZyV+cxeswKEjur1baGcuc8O66bQsM10C0Wa6jy4oG2E7gXkXeAxdOdhmLkMBPxWSLJyFj5vBKJLURAGJ58m0NKNcuLh01UgLLvXU87CWSEQVlDUSOHu/gQp2xgaTSAidRFISICjl83UiyVYl3/NIdHiKQZy73pNEIq4BqTNzZht2w8sCISjXWjnqYtcEZtLwTBM9c2Qci5I+ouDYs2sQMGPZxH+Y5kGiFIE6nskp4LwEPcmTpaBd99MqZTiLHPK2wwRDAQq5sxVjeS+enMBSGhAzMRhQsTIUOK1Lz9w2cWHZqy+YSevkMiknWvSMRfZoGg2mX1ecBA6yHupCyRCEqDkasaqMYsYc/LGRwWUmdHd7j4dG/x4ukIiE3HQ382KVDF546NAN9XHSmQsWo65wkbmuFSdxcdCtQ7yKP2ZgzLdx9dc19kSEbFqF0mzdsYuDgydf/I/RW8m324jPGUgPPgsoTPz0Af5MNn0p5ZgZpDJ9F6QfI2ztxQf/TT3DS+2J8Hm8b/sYAJxmXeCzJukikdnpcUUG5BeKKzQnfpf0UJUX4gmpyaNdVoQJlWzYSGGG9I5Fz0mXtoJGEh9sPc70ZZErBrN+0AMyyTCkkEwr1BJe1hOwnfysEiQyl5dMWneqlp8iGGCstyI4YLIVKT4gwfDJmvMTHDrIUP44FWz4JbEe93vnIUJXlSHyUDi92rnps1c+/LcgBiG7OIghqu6KHHXYxZlMsLLfpAzlAGTfjB0ICzlgLq0jqO5rGbnIAudtU+KqpAfKiI25XghCM3cuYlvn34+D2Qil5rqKDZlWRY/BA97CkM4aWRb89Pz2+eBsIHMedab1smks62fogs0+JMSDmL+3RH080B8a9qDCJMVvXrehgiu6yiP+pRN0epEgQi3SeUkkgeXXUOuDmdWBn7Wbuh5Gz2U67JtgsvqomUdtw4RQnNx3hMNJ269QS2iXRN7DrmUmXXGIYr+48knBqoTLUR4xztTXzRU73OgSPvSmov27OscELCEQWBgQM1hrjqc2tR+EPx1ojgVZMJTc+hzQzXl2sCc0pVMFkDRLa85iHbWyQe0Xoau1rkrg0AMk5VU5pJCmeXOILR9CMGCJ7cL5TuDJCVReDe7Aoi5K8hUUwKYc4A0MoXCLRy/+vHOIKBYPnXnbVk7BY1KS78zCKPNJShmY/9pjo0ToJjW/PErtJHxniCCjjtAxMBds9LXcrYCIZjFau4PAqURxwg+bDvvuJ/WdeiiEGW8PYge9GSEL7yjMNxOlLGd87XjGi3jriC4k4tHY8H5Gn94GUtc56QiCBn5eGcQMHRB9epEe2yDE0boe4y2i0f8jUcBkPV2IHg2nmHDkwk+uAqD573Q1dps0WAqYPTLi0L7r0CAAXs4NR3vxy8mi+fDAKRQI0AZ7wgyD7j8AQ/O0bMjrDFL8cjeYu0m+KEDux2IyLo4qFM0Q6R4GKnbgbQ3BDE6UdRsXpxWdblIrN00p0fiuBfIpCMGbtIafHwS8UAkYaHG2uLpRHBcKzqvW4GM6Skxhs62a6R7fh0fPgyZripARnK8NwOJ8gh9UXz00K0fn5p2v1uUXXZp771AhN6cc8PZLt4ejFJ+3INV8fm3cQkl7nqngOj9le7jJ8ARAwgqF0HFhxDHDq775Vp0SgGb/308XEEjg5KLbUgmo1Kdx8hSlRuBOHlU2bPfBp8GzSIGPn1o246e3BvBB9usKLwPCHPHqPAx42C1thAIkTQKn80fF6tsNtHiTiB0imelAQlBIluBOJmAVPBRXWXL6QM3ATGYslPhKpNEmq1AnJ04kI2vvQnIxAftXWofQRYUyGZxOJMDOXZjd+4BYnU6mZdApOw3AulwcAWR2O2ib9EOEoNOSSCqFi1f4ViXbL2Lokki3ka2MrkDiKryg5IIgqePRpxRozYUjmQxi9o+Pb1e3/tVVTG1yaJuGZz2IHt/nGoEN9zQbBe1di53NOCEi3p3vbwbX8oD7n1PkzfwH5RljX7iDs7fMDQ5yHrrtrmpLFeDyKraqDbpFk6pkRKsO04NckYBJW8a5bZCpWh9s7HrXpMzfhVEVdX2RtLENhpJJSWNcUKMkBqqppgTBmKBPGVEVeu68UIQ4NjPLwtjtUg08KOx2dCK3eQ2SOQtSAMkciHIUlX9/tMmkRQUXiB7JwtlbpbPXwBiqqra3cZVxUlnSaPCHwCLPzo/jYp1JUi/U6yuwZltNH6uPxh8YuXRHKcRdMsCSHsViK0KjzUqWSWMvt8bj5EHY3LR3MfWdt1yGUiVCQRFUdGXBNWqjklU6KhkOmUpD4Yqq1uvAmkAZHVdBZrXBhQ0CXcBDmcm2y4c+uHCnGxIVJZNlfVWkIpcVgf330HY0e19UIqyODMpyUGzlkwYWb4FkfFFtv7/QSwtP0CYTFCUxq877VpzgWASmWXAdtN7fCdIUKcyUEBo6StSKU9i8s6Q7Lyboiw4a9JhfL8KpE/j/3Lr7WMzyJHEiqTzAjEuoy+cs/Nc14CYqjoK62AxMnnbPqTAVC+iQHBQOUbFctnYUjFXSYQU6yD36vNAntTL0sCzhvL57d03arfP8GaJVJu/fu03xUnn1KtznSGXCO/vPVYmS3uljWx1q/eRJQ/mfr6sT+ibIy+LFZZpr/VayyZE7lPCzk2XpQmznwxffulova/FkUIk3VFxAiWIT+jlZwOL15eOcftSZK+KpR94MaNkVmF9MggQQ7y5EERVpXKBoZfeyNhYmXjVOjYRTFXaC0G8SIKb2lbvnYzlFU2PX7y977TotZr1FZDFk7ipnoWhLzJUJqBO1BmiXpYfxVyuGzdNzKUglMgHmWQRfWloSDmkYW6BaZwppryeJenYi8eBfqn50ESZNMFARuUyYhnbV2qbBVuXpjQuczdF+nhVO6j3JIszENO4MCkzmx59C3VbpvuWtrUvHr/+9QZdcMPGyUJu2gtyN4U5erV1wZHlLx7H/NWWaRNAKK3fh2572IaIFkNiMXcACb4LKI5KCih8q+PH7QxVV0v36pHlX99WMLLaBfmi8D2I5ytOlZYY6ZtXv2rhOztWNghlp1gdvpxgr1ApnR9f/qaFb+0hRqFsh6tjMNmJIo+J9uWvI/nm9vQaUfIb3JQG0imXz2fRsHn5C2K+e2DArH1QsNhvGKuUR462OWhsr/Llbyf4yaEaGR2Yu83gsVaftLgMUtqN4b/hFR4/O69lk1iUsVTTG+VFofbbz+YN73776VFAH99dG1Iu7l09Uh1bdCdf/wqlXxyXHRML5sD/GBD/jpfx/fJsvOttu589vnXv2KhAIBgYQQNfNg//hBdyQcio+vCjxxpks1gLApmqj+rjox0/5G1BgteVfbaPhTjR6Okwl/kAFtl/9PcGyWqpPutEYFW1dM5CAARkcneJlDwLlVP+dVDhMNdHW8mP45TzriBZ7k+Xi4W9kbMS0v5JkDdeD8gD8oA8IA/IA/KAPCAPyAPygDwgD8gD8oA8IA/IA/IXr/8JMAAhf0RDrOWy2QAAAABJRU5ErkJggg==",
"$Meta": {
"Type": "ActionTemplate"
}
}
Page updated on Monday, April 6, 2015