SSIS catalog deployment from source server to target - powershell

  • iKvfpMWsaoC1Gy8

    SSC Rookie

    Points: 44

    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: 90260

    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 Rookie

    Points: 44

    wow, thanks Sue - reading through it now!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply