SQL - Execute Script

To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.

{
  "Id": "73f89638-51d1-4fbb-b68f-b71ba9e86720",
  "Name": "SQL - Execute Script",
  "Description": "Execute a SQL script",
  "Version": 6,
  "ExportedAt": "2022-01-21T11:31:23.426Z",
  "ActionType": "Octopus.Script",
  "Author": "harrisonmeister",
  "Parameters": [
    {
      "Id": "802db0cd-f6eb-4cc6-9388-c5f6a4ae63ea",
      "Name": "ConnectionString",
      "Label": "Connection string",
      "HelpText": "Connection string for the SQL connection. Example:\n\n    Server=.\\SQLExpress;Database=OctoFX;Integrated Security=True;\n\nBind to a variable to provide different values for different environments.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "ec5a5223-3415-4cda-a888-3655f2b3e98c",
      "Name": "SqlScript",
      "Label": "SQL Script",
      "HelpText": "Script to run. Can be bound to a variable split over multiple lines. Text output by the PRINT statement in SQL will be logged to the deployment log. Use 'GO' to separate multiple commands\n\nExample:\n\n    USE MASTER\n    go\n\n    BACKUP DATABASE [OctoFX] TO DISK = N'#{FilePath}' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'Backup created by Octopus', SKIP, NOREWIND, NOUNLOAD,  STATS = 10",
      "DefaultValue": "PRINT 'Hello from SQL'",
      "DisplaySettings": {
        "Octopus.ControlType": "MultiLineText"
      }
    },
    {
      "Id": "1360f453-ad5b-4cc9-bf56-c565f62e2542",
      "Name": "ContinueOnError",
      "Label": "Continue On Error",
      "HelpText": "If set to true, an error with the SQL statement will simply write to the log and not cause an error in the deployment.",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "d2f77e7a-a3d2-453c-a784-e3587db28e12",
      "Name": "CommandTimeout",
      "Label": "Command Timeout",
      "HelpText": "The SQL Command Timeout. By default is 30 seconds.",
      "DefaultValue": "30",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "90b930a8-39d9-43a2-991d-e2a7d374d30b",
      "Name": "CaptureOutputToVariables",
      "Label": "Capture SQL Output to Variable(s)",
      "HelpText": "If set to true, output received from each command (via the `SqlInfoMessageEventHandler`) will be added to an Octopus [output variable](https://octopus.com/docs/projects/variables/output-variables) named **SQLOutput**, and will be suffixed with a number corresponding to the command that generated the output. The default is `False`.\n\nExample:\n\n1. `#{Octopus.Action[STEP NAME].Output.SQLOutput-1}`\n2. `#{Octopus.Action[STEP NAME].Output.SQLOutput-2}`",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptBody": "# Parameters\n$ConnectionString = $OctopusParameters[\"ConnectionString\"]\n$ContinueOnError = $OctopusParameters[\"ContinueOnError\"] -ieq \"True\"\n$SqlQuery = $OctopusParameters[\"SqlScript\"]\n$CommandTimeout = $OctopusParameters[\"CommandTimeout\"]\n$CaptureOutputToVariables = $OctopusParameters[\"CaptureOutputToVariables\"] -ieq \"True\"\n\n# Local Variables\n$connection = New-Object System.Data.SqlClient.SqlConnection\n$connection.ConnectionString = $ConnectionString\n$StepName = $OctopusParameters[\"Octopus.Step.Name\"]\n\n$global:outputs = @()\n\nRegister-ObjectEvent -InputObject $connection -EventName InfoMessage -Action {\n    Write-Output $event.SourceEventArgs\n} | Out-Null\n\nfunction Execute-SqlQuery($SqlQuery) {\n    $queries = [System.Text.RegularExpressions.Regex]::Split($SqlQuery, \"^s*GOs*`$\", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)\n\n    $queries | ForEach-Object {\n        $query = $_\n        if ((-not [String]::IsNullOrWhiteSpace($query)) -and ($query.Trim() -ine \"GO\")) {            \n            $command = $connection.CreateCommand()\n            $command.CommandText = $query\n            $command.CommandTimeout = $CommandTimeout\n            $command.ExecuteNonQuery() | Out-Null\n        }\n    }\n}\n\n$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { param($sender, $event) \n    $eventMessage = $event.Message\n    Write-Verbose $eventMessage\n    if ($CaptureOutputToVariables -eq $True) {\n        $global:outputs += $eventMessage\n    }\n}\n\ntry {\n\t\n    Write-Output \"Attach InfoMessage event handler\"\n    $connection.add_InfoMessage($handler)\n    \n    Write-Output \"Connecting\"\n    $connection.Open()\n\n    Write-Output \"Executing script\"\n    Execute-SqlQuery -SqlQuery $SqlQuery\n}\ncatch {\n    if ($ContinueOnError) {\n        Write-Output \"Error: $($_.Exception.Message)\"\n    }\n    else {\n        throw\n    }\n}\nfinally {\n    if ($null -ne $connection) {\n        Write-Output \"Detach InfoMessage event handler\"\n        $connection.remove_InfoMessage($handler)\n        Write-Output \"Closing connection\"\n        $connection.Dispose()\n    }\n}\n\nif ($CaptureOutputToVariables -eq $True) {\n    Write-Output \"Capture output to variables is true\"\n    Write-Output \"Output Count: $($global:outputs.Length)\"\n    if ($global:outputs.Length -gt 0) {\n        Write-Verbose \"Setting Octopus output variables\"\n        for ($i = 0; $i -lt $global:outputs.Length; $i++) {\n            $variableName = \"SQLOutput-$($i+1)\"\n            $variableValue = $global:outputs[$i]\n            Set-OctopusVariable -Name $variableName -Value $variableValue\n            Write-Verbose \"Created output variable: ##{Octopus.Action[$StepName].Output.$variableName}\"\n        }\n    }\n    else {\n        Write-Verbose \"No Octopus output variables to set\"\n    }\n}",
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptSource": "Inline"
  },
  "Category": "SQL Server",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/sql-execute-script.json",
  "Website": "/step-templates/73f89638-51d1-4fbb-b68f-b71ba9e86720",
  "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"
  }
}

History