Octopus.Script exported 2017-03-20 by tylerrbrown belongs to ‘SQL Server’ category.
Given a path to a folder containing SQL scripts, this module will execute each script on the database server and catalog provided. It will execute them in order based on their name.
Parameters
When steps based on the template are included in a project’s deployment process, the parameters below can be set.
Connection String
ConnectionString =
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.
Continue On Error
ContinueOnError = "False"
If set to true, an error with the SQL statement will simply write to the log and not cause an error in the deployment.
Individual Go Timeout
CommandTimeout = 0
Each individual go statements timeout value in seconds.
A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).
Path to Script Files
PathToScripts =
The path to the SQL script files you wish to execute.
Filename Version Reg Ex
VersionRegEx = Release(\d+)_(\d+)\.
The regular expression to extract major and minor version number from file name. Given Release2_61.sql, use “Release(\d+)_(\d+)\.”.
Script body
Steps based on this template will execute the following PowerShell script.
$paramContinueOnError = $OctopusParameters['ContinueOnError']
if($paramContinueOnError -eq $null) { $paramContinueOnError = 'False' }
$paramVersionRegEx = $OctopusParameters['VersionRegEx']
if($paramVersionRegEx -eq $null) { $paramVersionRegEx = 'Release(\d+)_(\d+)\.' }
$paramPathToScripts = $OctopusParameters['PathToScripts']
if($paramPathToScripts -eq $null) { throw "*** Path to scrips must be defined." }
$paramCommandTimeout = $OctopusParameters['CommandTimeout']
if($paramCommandTimeout -eq $null) { $paramCommandTimeout = '0' }
$paramConnectionString = $OctopusParameters['ConnectionString']
if($paramConnectionString -eq $null) { throw "*** Connection string must be defined." }
$continueOnError = $paramContinueOnError.ToLower() -eq 'true'
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $paramConnectionString
Register-ObjectEvent -inputobject $connection -eventname InfoMessage -action {
write-host $event.SourceEventArgs
} | Out-Null
function Execute-SqlQuery($fileName)
{
Write-Host "Executing scripts in file '$fileName'"
$content = gc $fileName -raw
$queries = [System.Text.RegularExpressions.Regex]::Split($content, '\r\n\s*GO\s*\r\n', [System.Text.RegularExpressions.RegexOptions]::IgnoreCase) | ? { $_ -ne '' }
foreach($q in $queries)
{
if ((-not [String]::IsNullOrWhiteSpace($q)) -and ($q.Trim().ToLowerInvariant() -ne "go"))
{
$command = $connection.CreateCommand()
$command.CommandText = $q
$command.CommandTimeout = $paramCommandTimeout
$command.ExecuteNonQuery() | Out-Null
}
}
}
try
{
Write-Host "Executing scripts in folder '$paramPathToScripts'"
Write-Host "Sorting script files based on regular expression '$paramVersionRegEx'"
Write-Host "Opening SQL server connection..."
$connection.Open()
Get-ChildItem $paramPathToScripts *.sql |
% {
$matches = [System.Text.RegularExpressions.Regex]::Match($_.Name, $paramVersionRegEx, [System.Text.RegularExpressions.RegexOptions]::IgnoreCase )
new-object psobject -Property @{ "File"=$_; "Level1"=$matches.Groups[1]; "Level2"=$matches.Groups[2] }
} |
sort Level1, Level2 |
% {
Execute-SqlQuery -fileName $_.File.FullName
}
}
catch
{
if ($continueOnError)
{
Write-Host $_.Exception.Message
}
else
{
throw
}
}
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": "55aff8d8-61b4-4657-9a00-d002b394790e",
"Name": "SQL - Execute Scripts Ordered",
"Description": "Given a path to a folder containing SQL scripts, this module will execute each script on the database server and catalog provided. It will execute them in order based on their name.",
"Version": 64,
"ExportedAt": "2017-03-20T21:28:30.917Z",
"ActionType": "Octopus.Script",
"Author": "tylerrbrown",
"Parameters": [
{
"Id": "696365b9-e02e-49e9-8ff7-6d9145ec36df",
"Name": "ConnectionString",
"Type": "String",
"Label": "Connection String",
"HelpText": "Connection string for the SQL connection. Example:\n\nServer=.\\SQLExpress;Database=OctoFX;Integrated Security=True;\n\nBind to a variable to provide different values for different environments.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
},
"Links": {}
},
{
"Id": "004c2019-b733-4010-bf76-03f3757b1e9d",
"Name": "ContinueOnError",
"Type": "String",
"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": "SingleLineText"
},
"Links": {}
},
{
"Id": "52add130-b220-410f-8b46-e653e2b1ef2a",
"Name": "CommandTimeout",
"Type": "String",
"Label": "Individual Go Timeout",
"HelpText": "Each individual go statements timeout value in seconds.\n\nA value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).",
"DefaultValue": "0",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
},
"Links": {}
},
{
"Id": "19a1de2e-988c-4ccf-867d-bd64fb0338df",
"Name": "PathToScripts",
"Type": "String",
"Label": "Path to Script Files",
"HelpText": "The path to the SQL script files you wish to execute.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
},
"Links": {}
},
{
"Id": "1e09f697-563a-46a6-ad6d-0c1b2013d044",
"Name": "VersionRegEx",
"Type": "String",
"Label": "Filename Version Reg Ex",
"HelpText": "The regular expression to extract major and minor version number from file name. Given Release2_61.sql, use \"Release(\\d+)\\_(\\d+)\\\\.\".",
"DefaultValue": "Release(\\d+)_(\\d+)\\.",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
},
"Links": {}
}
],
"Properties": {
"Octopus.Action.Script.Syntax": "PowerShell",
"Octopus.Action.Script.ScriptSource": "Inline",
"Octopus.Action.RunOnServer": "false",
"Octopus.Action.Script.ScriptBody": "\n$paramContinueOnError = $OctopusParameters['ContinueOnError']\nif($paramContinueOnError -eq $null) { $paramContinueOnError = 'False' }\n\n$paramVersionRegEx = $OctopusParameters['VersionRegEx']\nif($paramVersionRegEx -eq $null) { $paramVersionRegEx = 'Release(\\d+)_(\\d+)\\.' }\n\n$paramPathToScripts = $OctopusParameters['PathToScripts'] \nif($paramPathToScripts -eq $null) { throw \"*** Path to scrips must be defined.\" }\n\n$paramCommandTimeout = $OctopusParameters['CommandTimeout'] \nif($paramCommandTimeout -eq $null) { $paramCommandTimeout = '0' }\n\n$paramConnectionString = $OctopusParameters['ConnectionString']\nif($paramConnectionString -eq $null) { throw \"*** Connection string must be defined.\" }\n\n$continueOnError = $paramContinueOnError.ToLower() -eq 'true'\n\n$connection = New-Object System.Data.SqlClient.SqlConnection\n$connection.ConnectionString = $paramConnectionString\n\nRegister-ObjectEvent -inputobject $connection -eventname InfoMessage -action {\n write-host $event.SourceEventArgs\n} | Out-Null\n\nfunction Execute-SqlQuery($fileName) \n{\n Write-Host \"Executing scripts in file '$fileName'\"\n\n $content = gc $fileName -raw\n $queries = [System.Text.RegularExpressions.Regex]::Split($content, '\\r\\n\\s*GO\\s*\\r\\n', [System.Text.RegularExpressions.RegexOptions]::IgnoreCase) | ? { $_ -ne '' }\n\n foreach($q in $queries)\n {\n if ((-not [String]::IsNullOrWhiteSpace($q)) -and ($q.Trim().ToLowerInvariant() -ne \"go\")) \n { \n $command = $connection.CreateCommand()\n $command.CommandText = $q\n $command.CommandTimeout = $paramCommandTimeout\n $command.ExecuteNonQuery() | Out-Null\n }\n }\n}\n\ntry \n{\n Write-Host \"Executing scripts in folder '$paramPathToScripts'\"\n\n Write-Host \"Sorting script files based on regular expression '$paramVersionRegEx'\"\n \n Write-Host \"Opening SQL server connection...\"\n $connection.Open()\n\n Get-ChildItem $paramPathToScripts *.sql |\n % { \n $matches = [System.Text.RegularExpressions.Regex]::Match($_.Name, $paramVersionRegEx, [System.Text.RegularExpressions.RegexOptions]::IgnoreCase )\n new-object psobject -Property @{ \"File\"=$_; \"Level1\"=$matches.Groups[1]; \"Level2\"=$matches.Groups[2] }\n } | \n sort Level1, Level2 |\n % {\n Execute-SqlQuery -fileName $_.File.FullName\n }\n}\ncatch \n{\n\tif ($continueOnError) \n\t{\n\t\tWrite-Host $_.Exception.Message\n\t}\n\telse \n\t{\n\t\tthrow\n\t}\n}\nfinally \n{\n Write-Host \"Closing connection.\"\n $connection.Dispose()\n}\n",
"Octopus.Action.Script.ScriptFileName": null,
"Octopus.Action.Package.FeedId": null,
"Octopus.Action.Package.PackageId": null
},
"Category": "SQL Server",
"HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/sql-execute-scripts-ordered.json",
"Website": "/step-templates/55aff8d8-61b4-4657-9a00-d002b394790e",
"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, March 20, 2017