SQL Server - Project Deployment Model - Deploy ISPAC

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

{
  "Id": "efe39ac7-3ab8-4f99-bfdc-aba342278d1a",
  "Name": "SQL Server - Project Deployment Model - Deploy ISPAC",
  "Description": "This is to deploy ssis packages using 'project deployment model' (ISPAC file )",
  "Version": 1,
  "ExportedAt": "2016-07-20T10:15:20.241+00:00",
  "ActionType": "Octopus.Script",
  "Author": "bobjwalker",
  "Parameters": [
    {
      "Name": "ISPAC_FILE_PATH",
      "Label": "Ispac file path",
      "HelpText": "Once the SSIS project is compiled \"ispac\" file gets created, this variable must hold the path of the ispac file.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "deploy.dts.server",
      "Label": "SSIS Server name",
      "HelpText": "SSIS Server name where this ssis packages must be deployed.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "SSIS_Folder",
      "Label": "SSIS Folder name",
      "HelpText": "SSIS folder name which is created under SSISDB",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "SSIS_Project",
      "Label": "Project Name",
      "HelpText": "SSIS Project name - this is the physical folder name where the OD is referring to .",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Name": "Environment_Name",
      "Label": "Environment Name",
      "HelpText": "This is the environment name where variables exists.",
      "DefaultValue": null,
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptSource": "Inline",
    "Octopus.Action.Script.ScriptBody": "#################################################################################################\n# Change source and destination properties\n#################################################################################################\n\n# Source\n$IspacFilePath = \"#{ISPAC_FILE_PATH}\"\n \n# Destination\n$SsisServer =   $OctopusParameters['deploy.dts.server'] \n$FolderName = $OctopusParameters['SSIS_Folder']\n$ProjectName = $OctopusParameters['SSIS_Project']\n\n# Environment\n$EnvironmentName = $OctopusParameters['Environment_Name']  \n$EnvironmentFolderName = $OctopusParameters['SSIS_Folder']\n\n\n# Replace empty projectname with filename\nif (-not $ProjectName)\n{\n  $ProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)\n}\n# Replace empty Environment folder with project folder\nif (-not $EnvironmentFolderName)\n{\n  $EnvironmentFolderName = $FolderName\n}\n\nclear\nWrite-Host \"========================================================================================================================================================\"\nWrite-Host \"==                                                         Used parameters                                                                            ==\"\nWrite-Host \"========================================================================================================================================================\"\nWrite-Host \"Ispac File Path        : \" $IspacFilePath\nWrite-Host \"SSIS Server            : \" $SsisServer\nWrite-Host \"Project Folder Path    : \" $FolderName\nWrite-Host \"Project Name           : \" $ProjectName\nWrite-Host \"Environment Name       : \" $EnvironmentName\nWrite-Host \"Environment Folder Path: \" $EnvironmentFolderName\nWrite-Host \"========================================================================================================================================================\"\nWrite-Host \"\"\n\n###########################\n########## ISPAC ##########\n###########################\n# Check if ispac file exists\nif (-Not (Test-Path $IspacFilePath))\n{\n    Throw  [System.IO.FileNotFoundException] \"Ispac file $IspacFilePath doesn't exists!\"\n}\nelse\n{\n    $IspacFileName = split-path $IspacFilePath -leaf\n    Write-Host \"Ispac file\" $IspacFileName \"found\"\n}\n\n\n############################\n########## SERVER ##########\n############################\n# Load the Integration Services Assembly\nWrite-Host \"Connecting to server $SsisServer \"\n$SsisNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\n[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;\n\n# Create a connection to the server\n$SqlConnectionstring = \"Data Source=\" + $SsisServer + \";Initial Catalog=master;Integrated Security=SSPI;\"\n$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring\n\n# Create the Integration Services object\n$IntegrationServices = New-Object $SsisNamespace\".IntegrationServices\" $SqlConnection\n\n# Check if connection succeeded\nif (-not $IntegrationServices)\n{\n  Throw  [System.Exception] \"Failed to connect to server $SsisServer \"\n}\nelse\n{\n   Write-Host \"Connected to server\" $SsisServer\n}\n\n\n#############################\n########## CATALOG ##########\n#############################\n# Create object for SSISDB Catalog\n$Catalog = $IntegrationServices.Catalogs[\"SSISDB\"]\n\n# Check if the SSISDB Catalog exists\nif (-not $Catalog)\n{\n    # Catalog doesn't exists. The user should create it manually.\n    # It is possible to create it, but that shouldn't be part of\n    # deployment of packages.\n    Throw  [System.Exception] \"SSISDB catalog doesn't exist. Create it manually!\"\n}\nelse\n{\n    Write-Host \"Catalog SSISDB found\"\n}\n\n\n############################\n########## FOLDER ##########\n############################\n# Create object to the (new) folder\n$Folder = $Catalog.Folders[$FolderName]\n\n# Check if folder already exists\nif (-not $Folder)\n{\n    # Folder doesn't exists, so create the new folder.\n    Write-Host \"Creating new folder\" $FolderName\n    $Folder = New-Object $SsisNamespace\".CatalogFolder\" ($Catalog, $FolderName, $FolderName)\n    $Folder.Create()\n}\nelse\n{\n    Write-Host \"Folder\" $FolderName \"found\"\n}\n\n\n#############################\n########## PROJECT ##########\n#############################\n# Deploying project to folder\nif($Folder.Projects.Contains($ProjectName)) {\n    Write-Host \"Deploying\" $ProjectName \"to\" $FolderName \"(REPLACE)\"\n}\nelse\n{\n    Write-Host \"Deploying\" $ProjectName \"to\" $FolderName \"(NEW)\"\n}\n# Reading ispac file as binary\n[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)\n$Folder.DeployProject($ProjectName, $IspacFile)\n$Project = $Folder.Projects[$ProjectName]\nif (-not $Project)\n{\n    # Something went wrong with the deployment\n    # Don't continue with the rest of the script\n    return \"\"\n}\n\n\n#################################\n########## ENVIRONMENT ##########\n#################################\n# Check if environment name is filled\nif (-not $EnvironmentName)\n{\n    # Kill connection to SSIS\n    $IntegrationServices = $null \n\n    # Stop the deployment script\n    Return \"Ready deploying $IspacFileName without adding environment references\"\n}\n\n# Create object to the (new) folder\n$EnvironmentFolder = $Catalog.Folders[$EnvironmentFolderName]\n\n# Check if environment folder exists\nif (-not $EnvironmentFolder)\n{\n  Throw  [System.Exception] \"Environment folder $EnvironmentFolderName doesn't exist\"\n}\n\n# Check if environment exists\nif(-not $EnvironmentFolder.Environments.Contains($EnvironmentName))\n{\n  Throw  [System.Exception] \"Environment $EnvironmentName doesn't exist in $EnvironmentFolderName \"\n}\nelse\n{\n    # Create object for the environment\n    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]\n\n    if ($Project.References.Contains($EnvironmentName, $EnvironmentFolderName))\n    {\n        Write-Host \"Reference to\" $EnvironmentName \"found\"\n    }\n    else\n    {\n        Write-Host \"Adding reference to\" $EnvironmentName\n        $Project.References.Add($EnvironmentName, $EnvironmentFolderName)\n        $Project.Alter() \n    }\n}\n\n\n########################################\n########## PROJECT PARAMETERS ##########\n########################################\n$ParameterCount = 0\n# Loop through all project parameters\nforeach ($Parameter in $Project.Parameters)\n{\n    # Get parameter name and check if it exists in the environment\n    $ParameterName = $Parameter.Name\n    if ($ParameterName.StartsWith(\"CM.\",\"CurrentCultureIgnoreCase\")) \n    { \n        # Ignoring connection managers \n    } \n    elseif ($ParameterName.StartsWith(\"INTERN_\",\"CurrentCultureIgnoreCase\")) \n    { \n        # Optional:\n        # Internal parameters are ignored (where name starts with INTERN_) \n        Write-Host \"Ignoring Project parameter\" $ParameterName \" (internal use only)\" \n    } \n    elseif ($Environment.Variables.Contains($Parameter.Name))\n    {\n        $ParameterCount = $ParameterCount + 1\n        Write-Host \"Project parameter\" $ParameterName \"connected to environment\"\n        $Project.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)\n        $Project.Alter()\n    }\n    else\n    {\n        # Variable with the name of the project parameter is not found in the environment\n        # Throw an exeception or remove next line to ignore parameter\n        Throw  [System.Exception]  \"Project parameter $ParameterName doesn't exist in environment\"\n    }\n}\nWrite-Host \"Number of project parameters mapped:\" $ParameterCount\n\n\n########################################\n########## PACKAGE PARAMETERS ##########\n########################################\n$ParameterCount = 0\n# Loop through all packages\nforeach ($Package in $Project.Packages)\n{\n    # Loop through all package parameters\n    foreach ($Parameter in $Package.Parameters)\n    {\n        # Get parameter name and check if it exists in the environment\n        $PackageName = $Package.Name\n        $ParameterName = $Parameter.Name \n        if ($ParameterName.StartsWith(\"CM.\",\"CurrentCultureIgnoreCase\")) \n        { \n            # Ignoring connection managers \n        } \n        elseif ($ParameterName.StartsWith(\"INTERN_\",\"CurrentCultureIgnoreCase\")) \n        { \n            # Optional:\n            # Internal parameters are ignored (where name starts with INTERN_) \n            Write-Host \"Ignoring Package parameter\" $ParameterName \" (internal use only)\" \n        } \n        elseif ($Environment.Variables.Contains($Parameter.Name))\n        {\n            $ParameterCount = $ParameterCount + 1\n            Write-Host \"Package parameter\" $ParameterName \"from package\" $PackageName \"connected to environment\"\n            $Package.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)\n            $Package.Alter()\n        }\n        else\n        {\n            # Variable with the name of the package parameter is not found in the environment\n            # Throw an exeception or remove next line to ignore parameter\n            Throw  [System.Exception]  \"Package parameter $ParameterName from package $PackageName doesn't exist in environment\"\n        }\n    }\n}\nWrite-Host \"Number of package parameters mapped:\" $ParameterCount\n\n\n###########################\n########## READY ##########\n###########################\n# Kill connection to SSIS\n$IntegrationServices = $null \n\n\nReturn \"Ready deploying $IspacFileName \"",
    "Octopus.Action.Script.ScriptFileName": null,
    "Octopus.Action.Package.NuGetFeedId": null,
    "Octopus.Action.Package.NuGetPackageId": null
  },
  "Category": "SQL Server",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/sqlserver-project-deployment-model-ispac-deploy.json",
  "Website": "/step-templates/efe39ac7-3ab8-4f99-bfdc-aba342278d1a",
  "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