SQL - Execute SQL Script Files

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

{
  "Id": "2bd3b8ef-35b4-43e9-b6de-8e0c515f3f10",
  "Name": "SQL - Execute SQL Script Files",
  "Description": "Executes SQL script file(s) against the specified database using the `SQLServer` Powershell Module.  This template includes an `Authentication` selector and supports SQL Authentication, Windows Authentication, and Azure Managed Identity.\n\nNote: If the `SqlServer` PowerShell module is not present, the template will download a temporary copy to perform the task.",
  "Version": 6,
  "ExportedAt": "2024-07-12T22:26:51.480Z",
  "ActionType": "Octopus.Script",
  "Author": "twerthi",
  "Packages": [
    {
      "Id": "8473acaf-aaeb-4c23-923a-91f664290f16",
      "Name": "template.Package",
      "PackageId": null,
      "FeedId": null,
      "AcquisitionLocation": "Server",
      "Properties": {
        "Extract": "True",
        "SelectionMode": "deferred",
        "PackageParameterName": "template.Package",
        "Purpose": ""
      }
    }
  ],
  "Parameters": [
    {
      "Id": "1f2b60c9-b85c-4c23-a313-fc18e82cd500",
      "Name": "serverInstance",
      "Label": "Server Instance Name",
      "HelpText": "The SQL Server Instance name",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "9884b8c1-01a0-4c6f-97b1-ff5146fa0836",
      "Name": "dbName",
      "Label": "Database Name",
      "HelpText": "The database name",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "05dc20d9-f75c-4971-9efb-e9aaad82a3a9",
      "Name": "Authentication",
      "Label": "Authentication",
      "HelpText": "The authentication method",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "SqlAuthentication|SQL Authentication\nWindowsIntegrated|Windows Integrated\nAzureADManaged|Azure Active Directory Managed Identity"
      }
    },
    {
      "Id": "4c2fc1b4-bdd0-4a9a-adb1-da1e818e62bc",
      "Name": "Username",
      "Label": "Username",
      "HelpText": "The username to use to connect (only applies with SqlAuthentication selected)",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "819f9b19-042d-42d8-9d19-5f5bf28c06b7",
      "Name": "Password",
      "Label": "Password",
      "HelpText": "The password to use to connect (only applies with SqlAuthentication selected)",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Id": "dd22f955-8317-4d58-8173-fc7d44df1192",
      "Name": "SQLScripts",
      "Label": "SQL Scripts",
      "HelpText": "Provide the path to search for matching scripts, each one on a new line. Wildcards for filenames only are accepted, e.g.\n- `/Scripts/*.sql`\n- `/Scripts/SQL/Deploy*.sql`\n- `src/Permissions/Pre*Permissions.sql`\n\n**Please Note:** The step looks for files relative to the extracted package location, and does *not* recursively search the folder hierarchy.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "MultiLineText"
      }
    },
    {
      "Id": "3bfed638-6649-438f-a02b-353e36a63c87",
      "Name": "template.Package",
      "Label": "Package",
      "HelpText": "Package containing the SQL scripts to be executed.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Package"
      }
    },
    {
      "Id": "c6b85a12-bf2f-4963-8526-ebbe8f14707d",
      "Name": "ExecuteSQL.DisplaySQLServerOutput",
      "Label": "Display SQL Output",
      "HelpText": "You can display SQL Server message output, such as those that result from the SQL `PRINT` statement, by checking this parameter",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "52d8f897-696b-4f77-87b5-383d6ce559c3",
      "Name": "ExecuteSQL.TrustServerCertificate",
      "Label": "Trust Server Certificate",
      "HelpText": "Force connection to trust the server certificate.",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.ScriptBody": "\nfunction Get-ModuleInstalled {\n    # Define parameters\n    param(\n        $PowerShellModuleName\n    )\n\n    # Check to see if the module is installed\n    if ($null -ne (Get-Module -ListAvailable -Name $PowerShellModuleName)) {\n        # It is installed\n        return $true\n    }\n    else {\n        # Module not installed\n        return $false \n    }\n}\n\nfunction Get-NugetPackageProviderNotInstalled {\n    # See if the nuget package provider has been installed\n    return ($null -eq (Get-PackageProvider -ListAvailable -Name Nuget -ErrorAction SilentlyContinue))\n}\n\nfunction Install-PowerShellModule {\n    # Define parameters\n    param(\n        $PowerShellModuleName,\n        $LocalModulesPath\n    )\n    \n    # Set TLS order\n    [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls11 -bor [System.Net.SecurityProtocolType]::Tls12\n\n    # Check to see if the package provider has been installed\n    if ((Get-NugetPackageProviderNotInstalled) -ne $false) {\n        # Display that we need the nuget package provider\n        Write-Output \"Nuget package provider not found, installing ...\"\n        \n        # Install Nuget package provider\n        Install-PackageProvider -Name Nuget -Force\n    }\n\n    # Save the module in the temporary location\n    Save-Module -Name $PowerShellModuleName -Path $LocalModulesPath -Force\n}\n\n\nfunction Invoke-ExecuteSQLScript {\n\n    [CmdletBinding()]\n    param\n    (\n        [parameter(Mandatory = $true, Position = 0)]\n        [ValidateNotNullOrEmpty()]\n        [string]\n        $serverInstance,\n\n        [parameter(Mandatory = $true, Position = 1)]\n        [ValidateNotNullOrEmpty()]\n        [string]\n        $dbName,\n\n        [string]\n        $Authentication,\n\n        [string]\n        $SQLScripts,\n\n        [bool]\n        $DisplaySqlServerOutput,\n        \n        [bool]\n        $TrustServerCertificate\n    )\n    \n    # Check to see if SqlServer module is installed\n    if ((Get-ModuleInstalled -PowerShellModuleName \"SqlServer\") -ne $true) {\n        # Display message\n        Write-Output \"PowerShell module SqlServer not present, downloading temporary copy ...\"\n\n        # Download and install temporary copy\n        Install-PowerShellModule -PowerShellModuleName \"SqlServer\" -LocalModulesPath $LocalModules\n    }\n\n    # Display\n    Write-Output \"Importing module SqlServer ...\"\n\n    # Import the module\n    Import-Module -Name \"SqlServer\"\n    \n    $ExtractedPackageLocation = $($OctopusParameters['Octopus.Action.Package[template.Package].ExtractedPath'])\n\n    $matchingScripts = @()\n\n    # 1. Locate matching scripts\n    foreach ($SQLScript in $SQLScripts.Split(\"`n\", [System.StringSplitOptions]::RemoveEmptyEntries)) {\n        try {\n            \n            Write-Verbose \"Searching for scripts matching '$($SQLScript)'\"\n            $scripts = @()\n            $parent = Split-Path -Path $SQLScript -Parent\n            $leaf = Split-Path -Path $SQLScript -Leaf\n            Write-Verbose \"Parent: '$parent', Leaf: '$leaf'\"\n            if (-not [string]::IsNullOrWhiteSpace($parent)) {\n                $path = Join-Path $ExtractedPackageLocation $parent\n                if (Test-Path $path) {\n                    Write-Verbose \"Searching for items in '$path' matching '$leaf'\"\n                    $scripts += @(Get-ChildItem -Path $path -Filter $leaf)\n                }\n                else {\n                    Write-Warning \"Path '$path' not found. Please check the path exists, and is relative to the package contents.\"\n                }\n            }\n            else {\n                Write-Verbose \"Searching in root of package for '$leaf'\"\n                $scripts += @(Get-ChildItem -Path $ExtractedPackageLocation -Filter $leaf)\n            }\n    \n            Write-Output \"Found $($scripts.Count) SQL scripts matching input '$SQLScript'\"\n\n            $matchingScripts += $scripts\n        }\n        catch {\n            Write-Error $_.Exception\n        }\n    }\n    \n    # Create arguments hash table\n    $sqlcmdArguments = @{}\n\n\t# Add bound parameters\n    $sqlcmdArguments.Add(\"ServerInstance\", $serverInstance)\n    $sqlcmdArguments.Add(\"Database\", $dbName)\n    #$sqlcmdArguments.Add(\"Query\", $SQLScripts)\n    \n    if ($DisplaySqlServerOutput)\n    {\n    \tWrite-Host \"Adding Verbose to argument list to display output ...\"\n        $sqlcmdArguments.Add(\"Verbose\", $DisplaySqlServerOutput)\n    }\n    \n    if ($TrustServerCertificate)\n    {\n    \t$sqlcmdArguments.Add(\"TrustServerCertificate\", $TrustServerCertificate)\n    }\n\n    # Only execute if we have matching scripts\n    if ($matchingScripts.Count -gt 0) {\n        foreach ($script in $matchingScripts) {\n            $sr = New-Object System.IO.StreamReader($script.FullName)\n            $scriptContent = $sr.ReadToEnd()\n        \n            # Execute based on selected authentication method\n            switch ($Authentication) {\n                \"AzureADManaged\" {\n                    # Get login token\n                    Write-Verbose \"Authenticating with Azure Managed Identity ...\"\n                \n                    $response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' -Method GET -Headers @{Metadata = \"true\" } -UseBasicParsing\n                    $content = $response.Content | ConvertFrom-Json\n                    $AccessToken = $content.access_token\n                    \n                    $sqlcmdArguments.Add(\"AccessToken\", $AccessToken)\n\n                    break\n                }\n                \"SqlAuthentication\" {\n                    Write-Verbose \"Authentication with SQL Authentication ...\"\n                    $sqlcmdArguments.Add(\"Username\", $username)\n                    $sqlcmdArguments.Add(\"Password\", $password)\n\n                    break\n                }\n                \"WindowsIntegrated\" {\n                    Write-Verbose \"Authenticating with Windows Authentication ...\"\n                    break\n                }\n            }\n            \n            $sqlcmdArguments.Add(\"Query\", $scriptContent)\n            \n            # Invoke sql cmd\n            Invoke-SqlCmd @sqlcmdArguments\n        \n            $sr.Close()\n\n            Write-Verbose (\"Executed manual script - {0}\" -f $script.Name)\n        }\n    }\n}\n\n# Define PowerShell Modules path\n$LocalModules = (New-Item \"$PSScriptRoot\\Modules\" -ItemType Directory -Force).FullName\n$env:PSModulePath = \"$LocalModules$([System.IO.Path]::PathSeparator)$env:PSModulePath\"\n\nif (Test-Path Variable:OctopusParameters) {\n    Write-Verbose \"Locating scripts from the literal entry of Octopus Parameter SQLScripts\"\n    $ScriptsToExecute = $OctopusParameters[\"SQLScripts\"]\n    $DisplaySqlServerOutput = $OctopusParameters[\"ExecuteSQL.DisplaySQLServerOutput\"] -ieq \"True\"\n    $TemplateTrustServerCertificate = [System.Convert]::ToBoolean($OctopusParameters[\"ExecuteSQL.TrustServerCertificate\"])\n    \n    Invoke-ExecuteSQLScript -serverInstance $OctopusParameters[\"serverInstance\"] `\n        -dbName $OctopusParameters[\"dbName\"] `\n        -Authentication $OctopusParameters[\"Authentication\"] `\n        -SQLScripts $ScriptsToExecute `\n        -DisplaySqlServerOutput $DisplaySqlServerOutput `\n        -TrustServerCertificate $TemplateTrustServerCertificate\n}",
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptSource": "Inline",
    "Octopus.Action.RunOnServer": "false"
  },
  "Category": "SQL Server",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/sql-execute-sql-files.json",
  "Website": "/step-templates/2bd3b8ef-35b4-43e9-b6de-8e0c515f3f10",
  "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