{"id":70133,"date":"2017-02-27T17:32:10","date_gmt":"2017-02-27T17:32:10","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=70133"},"modified":"2021-06-03T16:45:22","modified_gmt":"2021-06-03T16:45:22","slug":"deploying-multiple-ssis-projects-via-powershell","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/deploying-multiple-ssis-projects-via-powershell\/","title":{"rendered":"Automating SSIS Project Deployment with PowerShell: ispac Files, SSISDB Catalog, and Multi-Project Scripts"},"content":{"rendered":"<h1>Overview<\/h1>\n<p>This article describes a way of using PowerShell to deploy multiple SSIS projects as part of a database or data warehouse deployment. It contains code snippets to illustrate some of the details, and so you can amend it for similar requirements if necessary.<\/p>\n<h2>System Integration Test<\/h2>\n<p>This set of scripts were developed in order to automate the process of setting up a test cell to verify the build via a System integration test. It was done to ensure that all the dependencies between several SSIS projects for the Data Warehouse systems were all accounted for.<\/p>\n<p>Before each test run, the entire Data Warehouse system was provisioned afresh within the SIT test environment.<\/p>\n<h2>Solution Overview<\/h2>\n<p>In this solution, I will explain how to develop a PowerShell script to deploy multiple SSIS projects \/ packages. You can use the script straight away to deploy the SSIS artefacts in a test environment. I recommend that you to read all the steps detailed in the article before you do so, and understand the consequence. If required, you may modify the script based on usage. This script has six steps to complete.<\/p>\n<h2>What is expected from the script ?<\/h2>\n<p>The deployment script will read the Server configuration details (XML) and then deploy the Integration Services Project Deployment (ISPAC) files to the target SSIS server. The server configuration file is an XML file which contains server names for several test environments. This script can be used to deploy the ISPAC files to other test environments such as System Unit Test, System Integration test and Regression Test environments.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"835\" height=\"447\" class=\"wp-image-70134\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-46.png\" \/><\/p>\n<h2>Solution in Detail<\/h2>\n<p>The screenshot below represents the steps involved at very high level.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-70135\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/word-image-47.png\" width=\"479\" height=\"310\" \/><\/p>\n<h2>Assumptions<\/h2>\n<p>We\u2019ll assume that the reader is already aware of the SSIS build automation which is detailed <a href=\"https:\/\/www.simple-talk.com\/sql\/ssis\/deployment-automation-for-sql-server-integration-services-ssis\/\">here<\/a>, and that the reader has got fair understanding of PowerShell.<\/p>\n<h3>Step 1: Reading Server Details from a Configuration file<\/h3>\n<p>It is always a good idea to maintain the server details in a separate configuration file (an XML). This configuration file can keep the details that are specific to each environment. A sample XML configuration file has been created for demonstration purpose.<\/p>\n<pre class=\"theme:vs2012 lang:xhtml decode:true \">&lt;Environments&gt;\r\n&lt;Environment Name=\"SIT\" ServerInstance=\"Localhost\\SQL2012\" SourceFileLocation=\"\\\\Server\\Data\\Files\"&gt;&lt;\/Environment&gt;\r\n&lt;Environment Name=\"TST\" ServerInstance=\"Localhost\\TST2012\" SourceFileLocation=\"\\\\TSTServer\\Data\\Files\"&gt;&lt;\/Environment&gt;\r\n&lt;Environment Name=\"RST\" ServerInstance=\"Localhost\\RST2012\" SourceFileLocation=\"\\\\RSTServer\\Data\\Files\"&gt;&lt;\/Environment&gt;\r\n&lt;\/Environments&gt;<\/pre>\n<p>The file maintains the details for SIT, System test and regression test environments. For each environment, we are maintaining such details as the Environment name, Server Instance Name and Source File Location.<\/p>\n<p>There are various advantages to maintaining the details in a separate file. The configuration file can be easily updated if there are any changes to the name of the server or any environment specific values. Also there is no need to change the code to update the environment-specific values.<\/p>\n<p>This PowerShell script will read an XML configuration file and, based on the given target environment, will print the values.<\/p>\n<pre class=\"\">$ConfigPath = \"E: \\Scripts\\ServerConfig.xml\";\r\n$TargetEnvironment = \"SIT\"\r\n[xml]$file = Get-Content $ConfigPath\r\n$Environments = $file.SelectNodes(\"\/Environments\/Environment\")\r\nForeach ($Environment in $Environments)\r\n{\r\n If ($Environment.Name -eq $TargetEnvironment)\r\n {\r\n Write-Host $Environment.Name\r\n Write-Host $Environment.ServerInstance\r\n Write-Host $Environment.SourceFileLocation\r\n }<\/pre>\n<h3>Step 2: Enabling CLR in the server<\/h3>\n<p>You must enable the CLR on the SQL Server before you create the catalog on the SSIS server. The following section of the PowerShell script will do this.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server .\\SQL2012\r\n$configuration = $SQLServer.Configuration\r\n$CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\nWrite-Host $CLRValue.ConfigValue\r\nIf ($CLRValue.ConfigValue -eq 0)\r\n{\r\n    Write-Host \"Enabling CLR.....\"\r\n    $CLRValue.ConfigValue = 1\r\n    $configuration.Alter()\r\n<\/pre>\n<h3>Step 3: Creating SSIS Catalog in SQL Server<\/h3>\n<p>As we are setting up a System Integration Test environment, we should first drop the existing SSIS catalog so as to ensure that the existing projects, packages and environment details are deleted completely.<\/p>\n<p>However if we are planning to reuse the same script for other environments, we might want to comment out the relevant sections. This will help to keep the existing Catalog and deploy the SSIS projects.<\/p>\n<p>This script has three variables that are used to store the values of the password for SQL Server and the SSIS Catalog. The SSIS catalog stores sensitive information such as the package password in the SSIS Catalog. Therefore we must protect the Catalog with a password.<\/p>\n<p>This script will first check whether the CLR is enabled in the server. If not, then the PowerShell script will enable the CLR: Then it will connect to the SQL server. Once the connection has been established, the script will check for the presence of Catalogues in the server.<\/p>\n<p>If the catalog is not available in the first place, then the script will continue to create a catalog on the target server, using the password supplied.<\/p>\n<p>If the catalog is available, then it will raise a warning and it will drop the entire catalog. This means that all the available SSIS projects, packages and environments will be completely removed. You must decide whether this is appropriate in your environment. Once the catalog has been dropped, then it will recreate the Catalog.<\/p>\n<h4>Script to create SSIS catalog<\/h4>\n<pre class=\"theme:powershell-ise lang:ps decode:true \">$SQLServerInstance = \"localhost\\SQL2012\"\r\n  $SSISCatalogPassword = \"PW0Rd4sSsIsCAt10g\"\r\n  $SQLServerConnectionString = \"Data Source=$SQLServerInstance;Initial Catalog=master;Integrated Security=SSPI;\"\r\n  # Pre-Requisite - Enable CLR before creating catalog\r\n  #Connect to SQL server and enable CLR, if this is not already enabled\r\n  Write-Host \"Enabling CLR on the SQL Server ....\"\r\n  $SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server $SQLServerInstance\r\n  $configuration = $SQLServer.Configuration\r\n  $CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\n  Write-Host $CLRValue.ConfigValue\r\n  If ($CLRValue.ConfigValue -eq 0)\r\n  {\r\n      Write-Host \"Enabling CLR on the server $SQLServerInstance\"\r\n      $CLRValue.ConfigValue = 1\r\n      $configuration.Alter()\r\n      Write-Host \"CLR enabled on the server $SQLServerInstance successfully\"\r\n  }\r\n  # Loading IntegrationServices Assembly .........\r\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.IntegrationServices\") | Out-Null;\r\n  # Store the value of IntegrationServices Assembly name in a string\r\n  # This will avoid to re type the same string again and again\r\n  $SSISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\r\n  Write-Host \"Trying to connect SQL Server....\"\r\n  # Create SQL Server connection based on the connection string\r\n  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnectionString\r\n  Write-Host \"SQL Server connection has been enabled successfully\"\r\n  # Create Integration Services object based on the SQL Server connection\r\n  Write-Host \"Trying to create a object for Integration Service....\"\r\n  $IntegrationServices = New-Object $SSISNamespace\".IntegrationServices\" $SQLServerConnection\r\n  Write-Host \"Integration service object has been created successfully\"\r\n  Write-Host \"Dropping existing SSIS Catalog on the server $SQLServerInstance\"\r\n  # Drop the existing catalog if it exists\r\n  if ($IntegrationServices.Catalogs.Count -gt 0)\r\n  {\r\n      Write-Host \"Warning !, all the SSIS projects and Packages will be dropped....\"\r\n      $IntegrationServices.Catalogs[\"SSISDB\"].Drop()\r\n      Write-Host \"Existing SSIS Catalog has been dropped successfully .\"\r\n  }\r\n  Write-Host \"Creating SSIS Catalog on the server $SQLServerInstance\"\r\n  $SSISCatalog = New-Object $SSISNamespace\".Catalog\" ($IntegrationServices, \"SSISDB\", $SSISCatalogPassword)\r\n  $SSISCatalog.Create()\r\n  Write-Host \"SSIS Catalog has been created successfully on the server $SQLServerInstance\"<\/pre>\n<p>The screenshot below below confirms that the SSIS catalog has been created successfully.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"370\" height=\"313\" class=\"wp-image-70136\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\SSIS_Catalog_Creation.png\" \/><\/p>\n<h3>Step 4: Reading ISPAC files from folder<\/h3>\n<p>The scripts is written with the assumption that the Data Warehouse solution has many SSIS projects and these are stored in many folders. The screenshot below shows several SSIS projects, each in its own folder.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"492\" height=\"178\" class=\"wp-image-70137\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment-1.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\SSIS_Folders.png\" \/><\/p>\n<p>In addition, each SSIS package has two package parameters called <strong>ServerName<\/strong> and <strong>SourceFileLocation<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"609\" height=\"143\" class=\"wp-image-70138\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment-2.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\Package_parameter.png\" \/><\/p>\n<h4>Listing SSIS Project files<\/h4>\n<p>Now we need to find the name and location of the ISPAC file in the folder. This PowerShell script will loop through the given folder and will list out the name of the ISPAC file and its full path. In later stage this code snippet will be integrated into the main code.<\/p>\n<h4>Script to print the project files in a given folder<\/h4>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$SSISFolder = \"C:\\WorkArea\\ SSIS\"\r\n  # Look for SSIS ISPAC file (with extension .ISPAC) and return the \r\n  # name of the SSIS ISPAC file and the directory full path\r\n  $SSISISPACFiles = Get-ChildItem $SSISFolder -Recurse -Filter \"*.ispac\"\r\n  Foreach ($SSISISPACFile in $SSISISPACFiles)\r\n  {\r\n      #Name of the SSIS ISPAC file , will be used to create SSIS folder\r\n      $SSISFolerName = $SSISISPACFile.BaseName;\r\n      #The name of the directory will be used to locate the ISPAC file\r\n      $SSISISPACFile.FullName;\r\n      \r\n      Write-Host $SSISISPACFile.BaseName; #Name of the ISPAC file\r\n      Write-Host $SSISISPACFile.FullName; # ISPAC file full path\r\n  }<\/pre>\n<h4>Creating SSIS Projects in the Catalog<\/h4>\n<p>In this step, we will be integrating the code from the previous step. The PowerShell script will help us to drop the existing catalog and create a new catalog. Then the script will search for the SSIS project files in the given folder. Once found, the name of the SSIS project file will be used to create a folder in the SSIS Catalog. As each and every project need to be deployed to a separate folder this setup will help.<\/p>\n<p>In addition, the full location of the folder will also be stored in a separate variable. This folder location will be used to derive the full path for the ISPAC file in the next step.<\/p>\n<h4>Script to read ISPAC files and to create a folders in the SSIS catalog<\/h4>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$SQLServerInstance = \"localhost\\SQL2012\"\r\n  $SSISCatalogPassword = \"PW0Rd4sSsIsCAt10g\"\r\n  $SQLServerConnectionString = \"Data Source=$SQLServerInstance;Initial Catalog=master;Integrated Security=SSPI;\"\r\n  # Pre-Requisite - Enable CLR before creating catalog\r\n  #Connect to SQL server and enable CLR, if this is not already enabled\r\n  Write-Host \"Enabling CLR on the SQL Server ....\"\r\n  $SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server $SQLServerInstance\r\n  $configuration = $SQLServer.Configuration\r\n  $CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\n  Write-Host $CLRValue.ConfigValue\r\n  If ($CLRValue.ConfigValue -eq 0)\r\n  {\r\n      Write-Host \"Enabling CLR on the server $SQLServerInstance\"\r\n      $CLRValue.ConfigValue = 1\r\n      $configuration.Alter()\r\n      Write-Host \"CLR enabled on the server $SQLServerInstance successfully\"\r\n  }\r\n  # Loading IntegrationServices Assembly .........\r\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.IntegrationServices\") | Out-Null;\r\n  # Store the value of IntegrationServices Assembly name in a string\r\n  # This will avoid to re type the same string again and again\r\n  $SSISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\r\n  Write-Host \"Trying to connect SQL Server....\"\r\n  # Create SQL Server connection based on the connection string\r\n  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnectionString\r\n  Write-Host \"SQL Server connection has been enabled successfully\"\r\n  # Create Integration Services object based on the SQL Server connection\r\n  Write-Host \"Trying to create a object for Integration Service....\"\r\n  $IntegrationServices = New-Object $SSISNamespace\".IntegrationServices\" $SQLServerConnection\r\n  Write-Host \"Integration service object has been created successfully\"\r\n  Write-Host \"Dropping existing SSIS Catalog on the server $SQLServerInstance\"\r\n  # Drop the existing catalog if it exists\r\n  if ($IntegrationServices.Catalogs.Count -gt 0)\r\n  {\r\n      Write-Host \"Warning !, all the SSIS projects and Packages will be dropped....\"\r\n      $IntegrationServices.Catalogs[\"SSISDB\"].Drop()\r\n      Write-Host \"Existing SSIS Catalog has been dropped successfully .\"\r\n  }\r\n  Write-Host \"Creating SSIS Catalog on the server $SQLServerInstance\"\r\n  $SSISCatalog = New-Object $SSISNamespace\".Catalog\" ($IntegrationServices, \"SSISDB\", $SSISCatalogPassword)\r\n  $SSISCatalog.Create()\r\n  Write-Host \"SSIS Catalog has been created successfully on the server $SQLServerInstance\"\r\n  $SSISFolder = \"C:\\WorkArea\\ SSIS\"\r\n  # Look for SSIS ISPAC file (with extension .ISPAC) and return the \r\n  # name of the SSIS ISPAC file and the directory full path\r\n  $SSISISPACFiles = Get-ChildItem $SSISFolder -Recurse -Filter \"*.ispac\"\r\n  Foreach ($SSISISPACFile in $SSISISPACFiles)\r\n  {\r\n      #Name of the SSIS ISPAC file , will be used to create SSIS folder\r\n      $SSISFolerName = $SSISISPACFile.BaseName;\r\n      #The name of the directory will be used to locate the ISPAC file\r\n      $SSISDirectory = $SSISISPACFile.FullName;\r\n      Write-Host \"Creating the directory $SSISFolerName in the SSIS Catalog........\"\r\n      $SSISFolderInCatalog = New-Object $SSISNamespace\".CatalogFolder\" ($SSISCatalog, $SSISFolerName, $SSISFolerName)\r\n      $SSISFolderInCatalog.Create()\r\n      Write-Host \"The directory $SSISFolerName has been successfully created in the SSIS Catalog........\"\r\n  }<\/pre>\n<p>The screenshot below confirms that folder has been created for each SSIS project.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"250\" height=\"420\" class=\"wp-image-70139\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment-3.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\SSIS_Folders_Created.png\" \/><\/p>\n<h3>Step 5: Deploy ISPAC files to SSIS catalog<\/h3>\n<p>This step integrates all the steps mentioned above (Step 1, Step 2 &amp; Step3)<\/p>\n<p>In this step the script will read the available ISPAC file for each SSIS project. The ISPAC file will be read into a Byte array variable and then the file will be deployed into the SSIS Catalog.<\/p>\n<h4>Script to deploy ISPAC files to the SSIS catalog<\/h4>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$SQLServerInstance = \"localhost\\SQL2012\"\r\n  $SSISCatalogPassword = \"PW0Rd4sSsIsCAt10g\"\r\n  $SQLServerConnectionString = \"Data Source=$SQLServerInstance;Initial Catalog=master;Integrated Security=SSPI;\"\r\n  # Pre-Requisite - Enable CLR before creating catalog\r\n  #Connect to SQL server and enable CLR, if this is not already enabled\r\n  Write-Host \"Enabling CLR on the SQL Server ....\"\r\n  $SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server $SQLServerInstance\r\n  $configuration = $SQLServer.Configuration\r\n  $CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\n  Write-Host $CLRValue.ConfigValue\r\n  If ($CLRValue.ConfigValue -eq 0)\r\n  {\r\n      Write-Host \"Enabling CLR on the server $SQLServerInstance\"\r\n      $CLRValue.ConfigValue = 1\r\n      $configuration.Alter()\r\n      Write-Host \"CLR enabled on the server $SQLServerInstance successfully\"\r\n  }\r\n  # Loading IntegrationServices Assembly .........\r\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.IntegrationServices\") | Out-Null;\r\n  # Store the value of IntegrationServices Assembly name in a string\r\n  # This will avoid to re type the same string again and again\r\n  $SSISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\r\n  Write-Host \"Trying to connect SQL Server....\"\r\n  # Create SQL Server connection based on the connection string\r\n  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnectionString\r\n  Write-Host \"SQL Server connection has been enabled successfully\"\r\n  # Create Integration Services object based on the SQL Server connection\r\n  Write-Host \"Trying to create a object for Integration Service....\"\r\n  $IntegrationServices = New-Object $SSISNamespace\".IntegrationServices\" $SQLServerConnection\r\n  Write-Host \"Integration service object has been created successfully\"\r\n  Write-Host \"Dropping existing SSIS Catalog on the server $SQLServerInstance\"\r\n  # Drop the existing catalog if it exists\r\n  if ($IntegrationServices.Catalogs.Count -gt 0)\r\n  {\r\n      Write-Host \"Warning !, all the SSIS projects and Packages will be dropped....\"\r\n      $IntegrationServices.Catalogs[\"SSISDB\"].Drop()\r\n      Write-Host \"Existing SSIS Catalog has been dropped successfully .\"\r\n  }\r\n  Write-Host \"Creating SSIS Catalog on the server $SQLServerInstance\"\r\n  $SSISCatalog = New-Object $SSISNamespace\".Catalog\" ($IntegrationServices, \"SSISDB\", $SSISCatalogPassword)\r\n  $SSISCatalog.Create()\r\n  Write-Host \"SSIS Catalog has been created successfully on the server $SQLServerInstance\"\r\n  $SSISFolder = \"C:\\WorkArea\\SSIS\"\r\n  # Look for SSIS ISPAC file (with extension .ISPAC) and return the \r\n  # name of the SSIS ISPAC file and the directory full path\r\n  $SSISISPACFiles = Get-ChildItem $SSISFolder -Recurse -Filter \"*.ispac\"\r\n  Foreach ($SSISISPACFile in $SSISISPACFiles)\r\n  {\r\n      #Name of the SSIS ISPAC file , will be used to create SSIS folder\r\n      $SSISFolerName = $SSISISPACFile.BaseName;\r\n      #The name of the directory will be used to locate the ISPAC file\r\n      $SSISDirectory = $SSISISPACFile.FullName;\r\n      Write-Host \"Creating the directory $SSISFolerName in the SSIS Catalog........\"\r\n      $SSISFolderInCatalog = New-Object $SSISNamespace\".CatalogFolder\" ($SSISCatalog, $SSISFolerName, $SSISFolerName)\r\n      $SSISFolderInCatalog.Create()\r\n      Write-Host \"The directory $SSISFolerName has been successfully created in the SSIS Catalog........\"\r\n      #Build the ISPAC file path from the SSIS directory path\r\n      $ISPACFilePath = $SSISISPACFile.FullName\r\n      \r\n      Write-Host \"Deploying \" $SSISFolerName \" project ...\"\r\n      # the ISPAC file will be read into a variable for deployment\r\n      [byte[]]$ISPACFile = [System.IO.File]::ReadAllBytes($ISPACFilePath)\r\n      $SSISFolderInCatalog.DeployProject($SSISFolerName, $ISPACFile)\r\n      Write-Host $SSISFolerName \" project has been deployed successfully\"\r\n      \r\n  }<\/pre>\n<p>The screenshot below confirms that all the SSIS projects have been deployed successfully.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"306\" height=\"488\" class=\"wp-image-70140\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment-4.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\SSIS_Projects_Created.png\" \/><\/p>\n<h3>Step 6: Setting up Environment for SSIS catalog<\/h3>\n<h4>Creating an Environment for the SSIS Project<\/h4>\n<p>This step will help us to create an environment for each SSIS Project and will assign the environment to the project. This consists of three parts. In the first part, we will be creating an environment for the SSIS project. In the second part, a set of Environment variables will be added to the Environment. In the last part, the SSIS project variable will be referenced to the Environment variable.<\/p>\n<p>An environment object can be created with the help of the<strong> EnvironmentInfo<\/strong> in the SSIS Integration assembly by passing the Folder name, Environment name and description.<\/p>\n<h4>Adding variables to the environment<\/h4>\n<p>Once an environment has been created, then variables can be added to it. A variable can be added by accessing the <strong>Variables.Add<\/strong> method by passing the arguments we\u2019ll mention soon.<\/p>\n<p>The<strong> Add<\/strong> methods accept variable name, datatype, default value, sensitivity and description as parameters.<\/p>\n<h4>Referencing the environment variables to SSIS Project<\/h4>\n<p>The last step will create an environment with variables. Now in this step, we will see a procedure to add the environment to the SSIS project.<\/p>\n<p>First the SSIS project has to be referenced to a variable using the <strong>Projects<\/strong> method in the catalog, by passing the SSIS project name. Now the environment can be linked by accessing the <strong>References.Add<\/strong> method and by passing the <strong>EnvironmentName<\/strong>.<\/p>\n<h4>Setting up package parameters using environment name<\/h4>\n<p>Now we can make use of the environment variables to link with the package parameters.<\/p>\n<p>The SSIS package has to be referenced to a local variable. Now the parameters method on the SSIS package can be used to link the <strong>Package<\/strong> parameter to the <strong>environment<\/strong> variable.<\/p>\n<h4>Script to create SSIS environment and setting up variables<\/h4>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$SQLServerInstance = \"localhost\\SQL2012\"\r\n  $SSISCatalogPassword = \"PW0Rd4sSsIsCAt10g\"\r\n  $SQLServerConnectionString = \"Data Source=$SQLServerInstance;Initial Catalog=master;Integrated Security=SSPI;\"\r\n  $EnvironmentName = \"SIT\"\r\n  # Pre-Requisite - Enable CLR before creating catalog\r\n  #Connect to SQL server and enable CLR, if this is not already enabled\r\n  Write-Host \"Enabling CLR on the SQL Server ....\"\r\n  $SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server $SQLServerInstance\r\n  $configuration = $SQLServer.Configuration\r\n  $CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\n  Write-Host $CLRValue.ConfigValue\r\n  If ($CLRValue.ConfigValue -eq 0)\r\n  {\r\n      Write-Host \"Enabling CLR on the server $SQLServerInstance\"\r\n      $CLRValue.ConfigValue = 1\r\n      $configuration.Alter()\r\n      Write-Host \"CLR enabled on the server $SQLServerInstance successfully\"\r\n  }\r\n  # Loading IntegrationServices Assembly .........\r\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.IntegrationServices\") | Out-Null;\r\n  # Store the value of IntegrationServices Assembly name in a string\r\n  # This will avoid to re type the same string again and again\r\n  $SSISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\r\n  Write-Host \"Trying to connect SQL Server....\"\r\n  # Create SQL Server connection based on the connection string\r\n  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnectionString\r\n  Write-Host \"SQL Server connection has been enabled successfully\"\r\n  # Create Integration Services object based on the SQL Server connection\r\n  Write-Host \"Trying to create a object for Integration Service....\"\r\n  $IntegrationServices = New-Object $SSISNamespace\".IntegrationServices\" $SQLServerConnection\r\n  Write-Host \"Integration service object has been created successfully\"\r\n  Write-Host \"Dropping existing SSIS Catalog on the server $SQLServerInstance\"\r\n  # Drop the existing catalog if it exists\r\n  if ($IntegrationServices.Catalogs.Count -gt 0)\r\n  {\r\n      Write-Host \"Warning !, all the SSIS projects and Packages will be dropped....\"\r\n      $IntegrationServices.Catalogs[\"SSISDB\"].Drop()\r\n      Write-Host \"Existing SSIS Catalog has been dropped successfully .\"\r\n  }\r\n  Write-Host \"Creating SSIS Catalog on the server $SQLServerInstance\"\r\n  $SSISCatalog = New-Object $SSISNamespace\".Catalog\" ($IntegrationServices, \"SSISDB\", $SSISCatalogPassword)\r\n  $SSISCatalog.Create()\r\n  Write-Host \"SSIS Catalog has been created successfully on the server $SQLServerInstance\"\r\n  $SSISFolder = \"C:\\WorkArea\\SSIS\"\r\n  # Look for SSIS ISPAC file (with extension .ISPAC) and return the \r\n  # name of the SSIS ISPAC file and the directory full path\r\n  $SSISISPACFiles = Get-ChildItem $SSISFolder -Recurse -Filter \"*.ispac\"\r\n  Foreach ($SSISISPACFile in $SSISISPACFiles)\r\n  {\r\n      #Name of the SSIS ISPAC file , will be used to create SSIS folder\r\n      $SSISFolerName = $SSISISPACFile.BaseName;\r\n      #The name of the directory will be used to locate the ISPAC file\r\n      $SSISDirectory = $SSISISPACFile.FullName;\r\n      Write-Host \"Creating the directory $SSISFolerName in the SSIS Catalog........\"\r\n      $SSISFolderInCatalog = New-Object $SSISNamespace\".CatalogFolder\" ($SSISCatalog, $SSISFolerName, $SSISFolerName)\r\n      $SSISFolderInCatalog.Create()\r\n      Write-Host \"The directory $SSISFolerName has been successfully created in the SSIS Catalog........\"\r\n      #Build the ISPAC file path from the SSIS directory path\r\n      $ISPACFilePath = $SSISISPACFile.FullName\r\n      \r\n      Write-Host \"Deploying \" $SSISFolerName \" project ...\"\r\n      # the ISPAC file will be read into a variable for deployment\r\n      [byte[]]$ISPACFile = [System.IO.File]::ReadAllBytes($ISPACFilePath)\r\n      $SSISFolderInCatalog.DeployProject($SSISFolerName, $ISPACFile)\r\n      Write-Host $SSISFolerName \" project has been deployed successfully\"\r\n      \r\n      Write-Host \"Setting up a environment for the project $SSISFolerName\"\r\n      $environment = New-Object $SSISNamespace\".EnvironmentInfo\" ($SSISFolderInCatalog, $EnvironmentName, \"Description\")\r\n      $environment.Create()\r\n      Write-Host \"Environment $EnvironmentName for the project $SSISFolerName has been created successfully\"\r\n      \r\n      #Let's add variables to the environment\r\n      #Usage example $environment.Variables.Add(variable name, type,     default value,     sensitivity,   description)\r\n      Write-Host \"Add variables for the environment $EnvironmentName \"\r\n      $environment.Variables.Add(\"ServerName\", [System.TypeCode]::String, \"Localhost\\SQL2012\", \"False\", \"Server Name\")\r\n      $environment.Variables.Add(\"SourceFileLocation\", [System.TypeCode]::String, \"\\\\Server\\Folder\\SourceFiles\", \"False\", \"Source File Location\")\r\n      $environment.Alter()\r\n      \r\n      $ServerName = $environment.Variables[\"ServerName\"];\r\n      $SourceFileLocation = $environment.Variables[\"SourceFileLocation\"];\r\n      \r\n      Write-Host \"Variables have been added for the environment $EnvironmentName \"\r\n      \r\n      Write-Host \"Refer the environment in the deployed SSIS Project\"\r\n      $SSISProject = $SSISFolderInCatalog.Projects[$SSISFolerName]\r\n      $SSISProject.References.Add($EnvironmentName, $SSISFolderInCatalog.Name)\r\n      $SSISProject.Alter()\r\n      \r\n      \r\n      $SSISPackage = $SSISProject.Packages[\"Package.dtsx\"]\r\n      $SSISPackage.Parameters[\"ServerName\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $ServerName.Name)\r\n      $SSISPackage.Parameters[\"SourceFileLocation\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $SourceFileLocation.Name)\r\n      $SSISPackage.Alter()\r\n      \r\n  }<\/pre>\n<p>From the screenshot below, you\u2019ll see that the SSIS projects have been deployed with the SSIS packages and the \u201cSIT\u201d environment has been created for each project.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"293\" height=\"402\" class=\"wp-image-70141\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment-5.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\SSIS_Environments.png\" \/><\/p>\n<p>This screenshot confirms that the environment \u201cSIT\u201d has been created with two variables.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"746\" height=\"490\" class=\"wp-image-70142\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment-6.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\5_Environment_Properties.png\" \/><\/p>\n<p>In addition the \u201cSIT\u201d environment has been referenced in the SSIS project.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"745\" height=\"490\" class=\"wp-image-70143\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/c-workarea-nat-sql-blog-simpletalk-sit-deployment-7.png\" alt=\"C:\\WorkArea\\Nat\\SQL\\Blog\\SimpleTalk\\SIT Deployment Script\\Iamges\\5_Environment_Project_reference.png\" \/><\/p>\n<h4>Enhancements to PowerShell script<\/h4>\n<p>The values of the key information such as the Server name and the data file location has been hardcoded in the deployment script. Now the deployment script will read the config. XML for key values. Based on the Target test Environment value (SIT, TST or RST), it will lookup the key values in the XML config file. The key values will be stored in few variables that can be used while creating a SSIS environment in the script.<\/p>\n<h4>Script to integrate XML config file with the PowerShell script<\/h4>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">$TargetEnvironment = \"SIT\"\r\n  $ConfigPath = \"C:\\WorkArea\\Scripts\\ServerConfig.xml\";\r\n  $SSISCatalogPassword = \"PW0Rd4sSsIsCAt10g\"\r\n  #Read the XML config file\r\n  [xml]$file = Get-Content $ConfigPath\r\n  $Environments = $file.SelectNodes(\"\/Environments\/Environment\")\r\n  Foreach ($Environment in $Environments)\r\n  {\r\n      If ($Environment.Name -eq $TargetEnvironment)\r\n      {\r\n          $TargetEnvironmentName = $Environment.Name\r\n          $TargetServerName = $Environment.ServerInstance\r\n          $TargetSourceFileLocation = $Environment.SourceFileLocation\r\n          \r\n      }\r\n  }\r\n  $SQLServerConnectionString = \"Data Source=$TargetServerName;Initial Catalog=master;Integrated Security=SSPI;\"\r\n  # Pre-Requisite - Enable CLR before creating catalog\r\n  #Connect to SQL server and enable CLR, if this is not already enabled\r\n  Write-Host \"Enabling CLR on the SQL Server ....\"\r\n  $SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server $SQLServerInstance\r\n  $configuration = $SQLServer.Configuration\r\n  $CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\n  Write-Host $CLRValue.ConfigValue\r\n  If ($CLRValue.ConfigValue -eq 0)\r\n  {\r\n      Write-Host \"Enabling CLR on the server $SQLServerInstance\"\r\n      $CLRValue.ConfigValue = 1\r\n      $configuration.Alter()\r\n      Write-Host \"CLR enabled on the server $SQLServerInstance successfully\"\r\n  }\r\n  # Loading IntegrationServices Assembly .........\r\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.IntegrationServices\") | Out-Null;\r\n  # Store the value of IntegrationServices Assembly name in a string\r\n  # This will avoid to re type the same string again and again\r\n  $SSISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\r\n  Write-Host \"Trying to connect SQL Server....\"\r\n  # Create SQL Server connection based on the connection string\r\n  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnectionString\r\n  Write-Host \"SQL Server connection has been enabled successfully\"\r\n  # Create Integration Services object based on the SQL Server connection\r\n  Write-Host \"Trying to create a object for Integration Service....\"\r\n  $IntegrationServices = New-Object $SSISNamespace\".IntegrationServices\" $SQLServerConnection\r\n  Write-Host \"Integration service object has been created successfully\"\r\n  Write-Host \"Dropping existing SSIS Catalog on the server $SQLServerInstance\"\r\n  # Drop the existing catalog if it exists\r\n  if ($IntegrationServices.Catalogs.Count -gt 0)\r\n  {\r\n      Write-Host \"Warning !, all the SSIS projects and Packages will be dropped....\"\r\n      $IntegrationServices.Catalogs[\"SSISDB\"].Drop()\r\n      Write-Host \"Existing SSIS Catalog has been dropped successfully .\"\r\n  }\r\n  Write-Host \"Creating SSIS Catalog on the server $SQLServerInstance\"\r\n  $SSISCatalog = New-Object $SSISNamespace\".Catalog\" ($IntegrationServices, \"SSISDB\", $SSISCatalogPassword)\r\n  $SSISCatalog.Create()\r\n  Write-Host \"SSIS Catalog has been created successfully on the server $SQLServerInstance\"\r\n  $SSISFolder = \"C:\\WorkArea\\SSIS\"\r\n  # Look for SSIS ISPAC file (with extension .ISPAC) and return the \r\n  # name of the SSIS ISPAC file and the directory full path\r\n  $SSISISPACFiles = Get-ChildItem $SSISFolder -Recurse -Filter \"*.ispac\"\r\n  Foreach ($SSISISPACFile in $SSISISPACFiles)\r\n  {\r\n      #Name of the SSIS ISPAC file , will be used to create SSIS folder\r\n      $SSISFolerName = $SSISISPACFile.BaseName;\r\n      #The name of the directory will be used to locate the ISPAC file\r\n      $SSISDirectory = $SSISISPACFile.FullName;\r\n      Write-Host \"Creating the directory $SSISFolerName in the SSIS Catalog........\"\r\n      $SSISFolderInCatalog = New-Object $SSISNamespace\".CatalogFolder\" ($SSISCatalog, $SSISFolerName, $SSISFolerName)\r\n      $SSISFolderInCatalog.Create()\r\n      Write-Host \"The directory $SSISFolerName has been successfully created in the SSIS Catalog........\"\r\n      #Build the ISPAC file path from the SSIS directory path\r\n      $ISPACFilePath = $SSISISPACFile.FullName\r\n      \r\n      Write-Host \"Deploying \" $SSISFolerName \" project ...\"\r\n      # the ISPAC file will be read into a variable for deployment\r\n      [byte[]]$ISPACFile = [System.IO.File]::ReadAllBytes($ISPACFilePath)\r\n      $SSISFolderInCatalog.DeployProject($SSISFolerName, $ISPACFile)\r\n      Write-Host $SSISFolerName \" project has been deployed successfully\"\r\n      \r\n      Write-Host \"Setting up a environment for the project $SSISFolerName\"\r\n      $environment = New-Object $SSISNamespace\".EnvironmentInfo\" ($SSISFolderInCatalog, $TargetEnvironment, \"Description\")\r\n      $environment.Create()\r\n      Write-Host \"Environment $TargetEnvironment for the project $SSISFolerName has been created successfully\"\r\n      \r\n      #Let's add variables to the environment\r\n      #Usage example $environment.Variables.Add(variable name, type,     default value,     sensitivity,   description)\r\n      Write-Host \"Add variables for the environment $TargetEnvironment \"\r\n      $environment.Variables.Add(\"ServerName\", [System.TypeCode]::String, $TargetServerName, \"False\", \"Server Name\")\r\n      $environment.Variables.Add(\"SourceFileLocation\", [System.TypeCode]::String, $TargetSourceFileLocation, \"False\", \"Source File Location\")\r\n      $environment.Alter()\r\n      \r\n      $ServerName = $environment.Variables[\"ServerName\"];\r\n      $SourceFileLocation = $environment.Variables[\"SourceFileLocation\"];\r\n      \r\n      Write-Host \"Variables have been added for the environment $TargetEnvironment \"\r\n      \r\n      Write-Host \"Refer the environment in the deployed SSIS Project\"\r\n      $SSISProject = $SSISFolderInCatalog.Projects[$SSISFolerName]\r\n      $SSISProject.References.Add($TargetEnvironment, $SSISFolderInCatalog.Name)\r\n      $SSISProject.Alter()\r\n      \r\n      \r\n      $SSISPackage = $SSISProject.Packages[\"Package.dtsx\"]\r\n      $SSISPackage.Parameters[\"ServerName\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $ServerName.Name)\r\n      $SSISPackage.Parameters[\"SourceFileLocation\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $SourceFileLocation.Name)\r\n      $SSISPackage.Alter()\r\n      \r\n  }<\/pre>\n<h4>Command line execution and passing parameters<\/h4>\n<p>Usually the release manager, or whoever holds responsibility for the release, always prefers to deploy SSIS projects from the command line. Because the deployment script has been prepared in PowerShell, it is possible to execute the script from the command line.<\/p>\n<p>In the previous script, we have hard-coded a number of variables, to make things simpler, but for the final version of the script, we can make the change to accept parameters for these values.<\/p>\n<h4>Script to run PowerShell script from command line with parameters<\/h4>\n<pre class=\"theme:powershell-ise lang:ps decode:true\"># Call the PowerShell script and pass the arguments\r\n  #.\\7_Deployment_Script_With_Arguments.ps1 \"SIT\" \"C:\\WorkArea\\Scripts\\ServerConfig.xml\" \"PW0Rd4sSsIsCAt10g\" \"C:\\WorkArea\\SSIS\"\r\n  #.\\7_Deployment_Script_With_Arguments.ps1 -TargetEnvironment \"SIT\" -ConfigPath \"C:\\WorkArea\\ Scripts\\ServerConfig.xml\" -SSISCatalogPassword \"PW0Rd4sSsIsCAt10g\" -SSISFolder \"C:\\WorkArea\\SSIS\"\r\n  [CmdletBinding()]\r\n  param (\r\n      [Parameter(Mandatory = $true)]\r\n      [string]$TargetEnvironment = $null,\r\n      [string]$ConfigPath = $null,\r\n      [string]$SSISCatalogPassword = $null,\r\n      [string]$SSISFolder = $null\r\n      \r\n  )\r\n  #$SSISFolder = \"C:\\WorkArea\\SSIS\"\r\n  #Read the XML config file\r\n  [xml]$file = Get-Content $ConfigPath\r\n  $Environments = $file.SelectNodes(\"\/Environments\/Environment\")\r\n  Foreach ($Environment in $Environments)\r\n  {\r\n      If ($Environment.Name -eq $TargetEnvironment)\r\n      {\r\n          $TargetEnvironmentName = $Environment.Name\r\n          $TargetServerName = $Environment.ServerInstance\r\n          $TargetSourceFileLocation = $Environment.SourceFileLocation\r\n          \r\n      }\r\n  }\r\n  $SQLServerConnectionString = \"Data Source=$TargetServerName;Initial Catalog=master;Integrated Security=SSPI;\"\r\n  # Pre-Requisite - Enable CLR before creating catalog\r\n  #Connect to SQL server and enable CLR, if this is not already enabled\r\n  Write-Host \"Enabling CLR on the SQL Server ....\"\r\n  $SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server $SQLServerInstance\r\n  $configuration = $SQLServer.Configuration\r\n  $CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\n  Write-Host $CLRValue.ConfigValue\r\n  If ($CLRValue.ConfigValue -eq 0)\r\n  {\r\n      Write-Host \"Enabling CLR on the server $SQLServerInstance\"\r\n      $CLRValue.ConfigValue = 1\r\n      $configuration.Alter()\r\n      Write-Host \"CLR enabled on the server $SQLServerInstance successfully\"\r\n  }\r\n  # Loading IntegrationServices Assembly .........\r\n  [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.IntegrationServices\") | Out-Null;\r\n  # Store the value of IntegrationServices Assembly name in a string\r\n  # This will avoid to re type the same string again and again\r\n  $SSISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\r\n  Write-Host \"Trying to connect SQL Server....\"\r\n  # Create SQL Server connection based on the connection string\r\n  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnectionString\r\n  Write-Host \"SQL Server connection has been enabled successfully\"\r\n  # Create Integration Services object based on the SQL Server connection\r\n  Write-Host \"Trying to create a object for Integration Service....\"\r\n  $IntegrationServices = New-Object $SSISNamespace\".IntegrationServices\" $SQLServerConnection\r\n  Write-Host \"Integration service object has been created successfully\"\r\n  Write-Host \"Dropping existing SSIS Catalog on the server $SQLServerInstance\"\r\n  # Drop the existing catalog if it exists\r\n  if ($IntegrationServices.Catalogs.Count -gt 0)\r\n  {\r\n      Write-Host \"Warning !, all the SSIS projects and Packages will be dropped....\"\r\n      $IntegrationServices.Catalogs[\"SSISDB\"].Drop()\r\n      Write-Host \"Existing SSIS Catalog has been dropped successfully .\"\r\n  }\r\n  Write-Host \"Creating SSIS Catalog on the server $SQLServerInstance\"\r\n  $SSISCatalog = New-Object $SSISNamespace\".Catalog\" ($IntegrationServices, \"SSISDB\", $SSISCatalogPassword)\r\n  $SSISCatalog.Create()\r\n  Write-Host \"SSIS Catalog has been created successfully on the server $SQLServerInstance\"\r\n  # Look for SSIS ISPAC file (with extension .ISPAC) and return the \r\n  # name of the SSIS ISPAC file and the directory full path\r\n  $SSISISPACFiles = Get-ChildItem $SSISFolder -Recurse -Filter \"*.ispac\"\r\n  Foreach ($SSISISPACFile in $SSISISPACFiles)\r\n  {\r\n      #Name of the SSIS ISPAC file , will be used to create SSIS folder\r\n      $SSISFolerName = $SSISISPACFile.BaseName;\r\n      #The name of the directory will be used to locate the ISPAC file\r\n      $SSISDirectory = $SSISISPACFile.FullName;\r\n      Write-Host \"Creating the directory $SSISFolerName in the SSIS Catalog........\"\r\n      $SSISFolderInCatalog = New-Object $SSISNamespace\".CatalogFolder\" ($SSISCatalog, $SSISFolerName, $SSISFolerName)\r\n      $SSISFolderInCatalog.Create()\r\n      Write-Host \"The directory $SSISFolerName has been successfully created in the SSIS Catalog........\"\r\n      #Build the ISPAC file path from the SSIS directory path\r\n      $ISPACFilePath = $SSISISPACFile.FullName\r\n      \r\n      Write-Host \"Deploying \" $SSISFolerName \" project ...\"\r\n      # the ISPAC file will be read into a variable for deployment\r\n      [byte[]]$ISPACFile = [System.IO.File]::ReadAllBytes($ISPACFilePath)\r\n      $SSISFolderInCatalog.DeployProject($SSISFolerName, $ISPACFile)\r\n      Write-Host $SSISFolerName \" project has been deployed successfully\"\r\n      \r\n      Write-Host \"Setting up a environment for the project $SSISFolerName\"\r\n      $environment = New-Object $SSISNamespace\".EnvironmentInfo\" ($SSISFolderInCatalog, $TargetEnvironment, \"Description\")\r\n      $environment.Create()\r\n      Write-Host \"Environment $TargetEnvironment for the project $SSISFolerName has been created successfully\"\r\n      \r\n      #Let's add variables to the environment\r\n      #Usage example $environment.Variables.Add(variable name, type,     default value,     sensitivity,   description)\r\n      Write-Host \"Add variables for the environment $TargetEnvironment \"\r\n      $environment.Variables.Add(\"ServerName\", [System.TypeCode]::String, $TargetServerName, \"False\", \"Server Name\")\r\n      $environment.Variables.Add(\"SourceFileLocation\", [System.TypeCode]::String, $TargetSourceFileLocation, \"False\", \"Source File Location\")\r\n      $environment.Alter()\r\n      \r\n      $ServerName = $environment.Variables[\"ServerName\"];\r\n      $SourceFileLocation = $environment.Variables[\"SourceFileLocation\"];\r\n      \r\n      Write-Host \"Variables have been added for the environment $TargetEnvironment \"\r\n      \r\n      Write-Host \"Refer the environment in the deployed SSIS Project\"\r\n      $SSISProject = $SSISFolderInCatalog.Projects[$SSISFolerName]\r\n      $SSISProject.References.Add($TargetEnvironment, $SSISFolderInCatalog.Name)\r\n      $SSISProject.Alter()\r\n      \r\n      \r\n      $SSISPackage = $SSISProject.Packages[\"Package.dtsx\"]\r\n      $SSISPackage.Parameters[\"ServerName\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $ServerName.Name)\r\n      $SSISPackage.Parameters[\"SourceFileLocation\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $SourceFileLocation.Name)\r\n      $SSISPackage.Alter()\r\n      \r\n  }<\/pre>\n<p>This script accepts parameters for <strong>TargetEnvironment<\/strong>, <strong>ConfigPath<\/strong>, <strong>SSISCatalogPassword<\/strong> and <strong>SSIS<\/strong> folder location. The PowerShell script can be executed in two ways with parameters.<\/p>\n<h4>By Position<\/h4>\n<p>In the first method, the parameter values can be provided with a space in between each parameter. Based on their position, the corresponding parameter values will be set and the script will be executed.<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true \">.\\7_Deployment_Script_With_Arguments.ps1 \"SIT\" \"C:\\WorkArea\\Scripts\\ServerConfig.xml\" \"PW0Rd4sSsIsCAt10g\" \"C:\\WorkArea\\SSIS\"<\/pre>\n<h4>By Parameters<\/h4>\n<p>In this method, we can specify the parameter name and the value together. This is the recommended method as it is easy enough to understand the parameter and values.<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true \">.\\7_Deployment_Script_With_Arguments.ps1 -TargetEnvironment \"SIT\" -ConfigPath \"C:\\WorkArea\\ Scripts\\ServerConfig.xml\" -SSISCatalogPassword \"PW0Rd4sSsIsCAt10g\" -SSISFolder \"C:\\WorkArea\\SSIS\"<\/pre>\n<h3>Logging PowerShell script output:<\/h3>\n<p>The supplied script will display the deployment progress in the console. This is very helpful, if you are planning to integrate the script with the CI tools. As the CI tools usually maintain their own log , the PowerShell script output information will be available on the CI log for analysis and investigation.<\/p>\n<p>However if you are planning to use the script for unattended deployment then the script can be enhanced with additional logging. The same script has been modified to incorporate a logging to capture the details in a text file.\u00a0<\/p>\n<p>A function \u201cWrite-Log\u201d has been added to the script to accept a string and write the details to a logfile. This function will be called during the deployment and the progress information will be supplied as a parameter.<\/p>\n<p>This is the updated script<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\"># Call the PowerShell script and pass the arguments\r\n#.\\8_Deployment_Script_With_Logging.ps1 \"SIT\" \"C:\\WorkArea\\Scripts\\ServerConfig.xml\" \"PW0Rd4sSsIsCAt10g\" \"C:\\WorkArea\\SSIS\"\r\n#.\\8_Deployment_Script_With_Logging.ps1 -TargetEnvironment \"SIT\" -ConfigPath \"C:\\WorkArea\\ Scripts\\ServerConfig.xml\" -SSISCatalogPassword \"PW0Rd4sSsIsCAt10g\" -SSISFolder \"C:\\WorkArea \\SSIS\"\r\n\r\n[CmdletBinding()]\r\nparam (\r\n    [Parameter(Mandatory = $true)]\r\n    [string]$TargetEnvironment = $null,\r\n    [string]$ConfigPath = $null,\r\n    [string]$SSISCatalogPassword = $null,\r\n    [string]$SSISFolder = $null\r\n    \r\n)\r\n\r\n\r\n$DeploymentLogfile = \".\\SSIS_DeploymentLogfile.log\"\r\n\r\nFunction Write-Log\r\n{\r\n    Param (\r\n        [string]$logOutput\r\n    )\r\n    \r\n    Add-content $DeploymentLogfile -value $logOutput\r\n}\r\n\r\n\r\n#Read the XML config file\r\n[xml]$file = Get-Content $ConfigPath\r\n$Environments = $file.SelectNodes(\"\/Environments\/Environment\")\r\nForeach ($Environment in $Environments)\r\n{\r\n    If ($Environment.Name -eq $TargetEnvironment)\r\n    {\r\n        $TargetEnvironmentName = $Environment.Name\r\n        $TargetServerName = $Environment.ServerInstance\r\n        $TargetSourceFileLocation = $Environment.SourceFileLocation\r\n        \r\n    }\r\n}\r\n\r\n$SQLServerConnectionString = \"Data Source=$TargetServerName;Initial Catalog=master;Integrated Security=SSPI;\"\r\n\r\n\r\n# Pre-Requisite - Enable CLR before creating catalog\r\n#Connect to SQL server and enable CLR, if this is not already enabled\r\nWrite-Log \"Enabling CLR on the SQL Server ....\"\r\n$SQLServer = New-Object Microsoft.SQLServer.Management.SMO.Server $SQLServerInstance\r\n$configuration = $SQLServer.Configuration\r\n$CLRValue = $SQLServer.Configuration.IsSqlClrEnabled\r\nWrite-Log $CLRValue.ConfigValue\r\nIf ($CLRValue.ConfigValue -eq 0)\r\n{\r\n    Write-Log \"Enabling CLR on the server $SQLServerInstance\"\r\n    $CLRValue.ConfigValue = 1\r\n    $configuration.Alter()\r\n    Write-Log \"CLR enabled on the server $SQLServerInstance successfully\"\r\n}\r\n\r\n# Loading IntegrationServices Assembly .........\r\n[System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.IntegrationServices\") | Out-Null;\r\n# Store the value of IntegrationServices Assembly name in a string\r\n# This will avoid to re type the same string again and again\r\n$SSISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\r\nWrite-Log \"Trying to connect SQL Server....\"\r\n# Create SQL Server connection based on the connection string\r\n$SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection $SQLServerConnectionString\r\nWrite-Log \"SQL Server connection has been enabled successfully\"\r\n# Create Integration Services object based on the SQL Server connection\r\nWrite-Log \"Trying to create a object for Integration Service....\"\r\n$IntegrationServices = New-Object $SSISNamespace\".IntegrationServices\" $SQLServerConnection\r\nWrite-Log \"Integration service object has been created successfully\"\r\nWrite-Log \"Dropping existing SSIS Catalog on the server $SQLServerInstance\"\r\n# Drop the existing catalog if it exists\r\nif ($IntegrationServices.Catalogs.Count -gt 0)\r\n{\r\n    Write-Log \"Warning !, all the SSIS projects and Packages will be dropped....\"\r\n    $IntegrationServices.Catalogs[\"SSISDB\"].Drop()\r\n    Write-Log \"Existing SSIS Catalog has been dropped successfully .\"\r\n}\r\nWrite-Log \"Creating SSIS Catalog on the server $SQLServerInstance\"\r\n$SSISCatalog = New-Object $SSISNamespace\".Catalog\" ($IntegrationServices, \"SSISDB\", $SSISCatalogPassword)\r\n$SSISCatalog.Create()\r\nWrite-Log \"SSIS Catalog has been created successfully on the server $SQLServerInstance\"\r\n\r\n\r\n# Look for SSIS ISPAC file (with extension .ISPAC) and return the \r\n# name of the SSIS ISPAC file and the directory full path\r\n$SSISISPACFiles = Get-ChildItem $SSISFolder -Recurse -Filter \"*.ispac\"\r\nForeach ($SSISISPACFile in $SSISISPACFiles)\r\n{\r\n    #Name of the SSIS ISPAC file , will be used to create SSIS folder\r\n    $SSISFolerName = $SSISISPACFile.BaseName;\r\n    #The name of the directory will be used to locate the ISPAC file\r\n    $SSISDirectory = $SSISISPACFile.FullName;\r\n    Write-Log \"Creating the directory $SSISFolerName in the SSIS Catalog........\"\r\n    $SSISFolderInCatalog = New-Object $SSISNamespace\".CatalogFolder\" ($SSISCatalog, $SSISFolerName, $SSISFolerName)\r\n    $SSISFolderInCatalog.Create()\r\n    Write-Log \"The directory $SSISFolerName has been successfully created in the SSIS Catalog........\"\r\n    #Build the ISPAC file path from the SSIS directory path\r\n    $ISPACFilePath = $SSISISPACFile.FullName\r\n    \r\n    Write-Log \"Deploying \" $SSISFolerName \" project ...\"\r\n    # the ISPAC file will be read into a variable for deployment\r\n    [byte[]]$ISPACFile = [System.IO.File]::ReadAllBytes($ISPACFilePath)\r\n    $SSISFolderInCatalog.DeployProject($SSISFolerName, $ISPACFile)\r\n    Write-Log $SSISFolerName \" project has been deployed successfully\"\r\n    \r\n    Write-Log \"Setting up a environment for the project $SSISFolerName\"\r\n    $environment = New-Object $SSISNamespace\".EnvironmentInfo\" ($SSISFolderInCatalog, $TargetEnvironment, \"Description\")\r\n    $environment.Create()\r\n    Write-Log \"Environment $TargetEnvironment for the project $SSISFolerName has been created successfully\"\r\n    \r\n    #Let's add variables to the environment\r\n    #Usage example $environment.Variables.Add(variable name, type,     default value,     sensitivity,   description)\r\n    Write-Log \"Add variables for the environment $TargetEnvironment \"\r\n    $environment.Variables.Add(\"ServerName\", [System.TypeCode]::String, $TargetServerName, \"False\", \"Server Name\")\r\n    $environment.Variables.Add(\"SourceFileLocation\", [System.TypeCode]::String, $TargetSourceFileLocation, \"False\", \"Source File Location\")\r\n    $environment.Alter()\r\n    \r\n    $ServerName = $environment.Variables[\"ServerName\"];\r\n    $SourceFileLocation = $environment.Variables[\"SourceFileLocation\"];\r\n    \r\n    Write-Log \"Variables have been added for the environment $TargetEnvironment \"\r\n    \r\n    Write-Log \"Refer the environment in the deployed SSIS Project\"\r\n    $SSISProject = $SSISFolderInCatalog.Projects[$SSISFolerName]\r\n    $SSISProject.References.Add($TargetEnvironment, $SSISFolderInCatalog.Name)\r\n    $SSISProject.Alter()\r\n    \r\n    \r\n    $SSISPackage = $SSISProject.Packages[\"Package.dtsx\"]\r\n    $SSISPackage.Parameters[\"ServerName\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $ServerName.Name)\r\n    $SSISPackage.Parameters[\"SourceFileLocation\"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $SourceFileLocation.Name)\r\n    $SSISPackage.Alter()\r\n    \r\n}\r\n<\/pre>\n<h4>Points to remember<\/h4>\n<p>This script is intended to provision a Data Warehouse for System Integrated testing (SIT). For this we usually drop any existing SSIS Projects and deploy all the SSIS projects with packages and environments. If the requirement is similar then this script can be used straight away to meet these requirements.<\/p>\n<p>However, if you don\u2019t want to drop existing SSIS projects, you may comment\/remove the code to drop the existing catalog.<\/p>\n<p>In addition you can check whether the specific SSIS project is available and delete the project if exists.<\/p>\n<p>You should always delete the existing SSIS projects for a clean deployment. This will help us to investigate and debug deployment-related issues more easily.<\/p>\n<h2>Summary<\/h2>\n<p>You should check this script in a sandbox environment. This will help you to understand the script in detail, and you can modify the script to make it work better in your real test environment.<\/p>\n<p>If you use a Continuous Integration tool within your organisation, you can easily integrate this script to add it to an automated deployment. In addition, if you have an automated test framework for SSIS projects, then you can deploy and run the test framework to validate the deployment.<\/p>\n<h2>Reference<\/h2>\n<p>SSIS Deployment Reference from Matt Masson ( <a href=\"http:\/\/www.mattmasson.com\/2012\/06\/publish-to-ssis-catalog-using-powershell\/\">http:\/\/www.mattmasson.com\/2012\/06\/publish-to-ssis-catalog-using-powershell\/<\/a>)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Automating SSIS project deployment with PowerShell &#8211; deploying .ispac files to the SSISDB catalog, iterating across multiple projects in a folder structure, configuring environments and references, and integrating with CI\/CD pipelines for automated ETL deployments.&hellip;<\/p>\n","protected":false},"author":189139,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,143516],"tags":[],"coauthors":[39133],"class_list":["post-70133","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-database-devops"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/189139"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=70133"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70133\/revisions"}],"predecessor-version":[{"id":79493,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/70133\/revisions\/79493"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=70133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=70133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=70133"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=70133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}