SSIS catalog deployment from source server to target - powershell

• iKvfpMWsaoC1Gy8

  SSC Enthusiast

  Points: 184

  More actions

  September 18, 2019 at 8:53 pm

  #3681231

  I am looking for a good solution for automating the deployment of SSIS packages through environments. Right now the deployment is very manual but I am hoping to find a powershell script that will copy packages from source to target servers (including all configurations and environment variables). Does anyone have any suggestions?

  I have found a lot of scripts for deploying an ispac but I am looking for more of a copy from one server to another. I have started working on a script for just that but figured I'd as around if anyone has something already.

  $target_server_name = 'target_server_name'
  $source_server_name = 'source_server_name'
  $target_database_name = 'target_database_name'
  $source_database_name = 'source_database_name'
  $environment_name = 'environment_name'
  $folder_name = 'folder_name'
  $project_name = 'project_name'
  $job_name = 'job_name'
  
  function Write-Message ($msg) {
  Write-Host ' [+] ' -ForegroundColor Yellow -NoNewline
  Write-Host $msg
  }
  
  Write-Host 'Starting deployment' -ForegroundColor DarkGray
  
  $namespace = 'Microsoft.SqlServer.Management.IntegrationServices'
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null
  
  Write-Message ("Connecting to integration services on '$source_server_name'")
  $source_conn_str = "Data Source={0};Initial Catalog=master;Integrated Security=SSPI;" -f $source_server_name
  $source_conn = New-Object System.Data.SqlClient.SqlConnection $source_conn_str
  
  $source_intg_serv = New-Object "$namespace.IntegrationServices" $source_conn
  
  $source_catalog = $source_intg_serv.Catalogs['SSISDB']
  
  $source_folder = $source_catalog.Folders[$folder_name]
  if(!$source_folder){
  Write-Message ("Source folder not found '$folder_name'")
  throw "The source SSIS folder did not exist."
  }
  
  $source_project = $folder.Projects[$ProjectName]
  if(!$source_project){
  Write-Message ("Source project not found '$source_project'")
  throw "The source SSIS project did not exist."
  }
  
  $target_conn_str = "Data Source={0};Initial Catalog=master;Integrated Security=SSPI;" -f $target_server_name
  $target_conn = New-Object System.Data.SqlClient.SqlConnection $target_conn_str
  $target_intg_serv = New-Object "$namespace.IntegrationServices" $target_conn
  $target_catalog = $target_intg_serv.Catalogs['SSISDB']
  $target_folder = $target_catalog.Folders[$folder_name]
  
  if(!$target_folder) {
  Write-Message 'Folder not found, creating folder ...'
  $folder = New-Object "$($namespace).CatalogFolder" ($target_catalog, $folder_name, $folder_name) 
  $folder.Create() 
  } else {
  Write-Message 'Folder found ...'
  }
  
  Write-Message 'Deploying project file ...'
  
  #still figuring this bit out... Will also need to grab environment variables if they do not exist in target. Hoping to add job creation as well but that is just a nice to have

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  September 18, 2019 at 9:14 pm

  #3681232

  You may want to take a look at dbatools - there is a Copy-DbaSsisCatalog cmdlet that might work for you. There is a decent amount of granularity if you read through it:

  Copy-DbaSsisCatalog

  Sue

   

• iKvfpMWsaoC1Gy8

  SSC Enthusiast

  Points: 184

  More actions

  September 18, 2019 at 9:19 pm

  #3681233

  wow, thanks Sue - reading through it now!