September 18, 2019 at 8:53 pm
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
September 18, 2019 at 9:14 pm
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:
Sue
September 18, 2019 at 9:19 pm
wow, thanks Sue - reading through it now!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply