WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Monday, February 25, 2013

Moving SSIS Projects in SQL Server 2012

How do you move SSIS packages from one server to another when using the SSIS Catalog on SQL Server 2012?

Before I start, here are the parameters of the post:
  1. We are using the "Integration Services Catalog" on SQL Server 2012
  2. We deploy from BIDS to staging and production - no "ispac" files
  3. We use the project deployment method

When confronted with this task recently, I immediately went off to www.google.com and started searching, I was unable to find anything for my current situation.  There were several posts together that helped get me started including this one by Matt Masson .  The complexity that was introduced was primarily around the fact that I could not figure out how to move SSIS packages without an "ispac" file.  This script is primarily courtesy of the post mentioned, but I then utilized a stored procedure from the SSIS catalog SSISDB.catalog.get_project to get the project bytes and load them on the destination server.

Hope this helps someone out in the future, although I would guess there is a method that doesn't require querying the catalog to get the project bytes, here is the script I came up with.

 $SourceServer = "SourceServer"  
 $DestinationServer = "DestServer"  
 $ProjectFolder = "ProjectFolderName"  
 $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"       
 [Reflection.Assembly]::Load("$ISNamespace,Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | out-null      
 Write-Host "Connecting to server ..."       
 # Create a connection to the server       
 $constr = "Data Source=$SourceServer;Initial Catalog=master;Integrated Security=SSPI;"       
 $destconstr = "Data Source=$DestinationServer;Initial Catalog=master;Integrated Security=SSPI;"  
 $con = New-Object System.Data.SqlClient.SqlConnection $constr       
 $destcon = New-Object System.Data.SqlClient.SqlConnection $destconstr       
 # Create the Integration Services object       
 $ssis = New-Object $ISNamespace".IntegrationServices" $con   
 $destssis = New-Object $ISNamespace".IntegrationServices" $destcon   
 #Grab the SSISDB catalog  
 $catalog = $ssis.Catalogs | Where-Object{$_.Name -eq "SSISDB"}  
 $destcatalog = $destssis.Catalogs | Where-Object{$_.Name -eq "SSISDB"}  
 #Grab the Folders where the project  
 $curFolder = $catalog.Folders | Where{$_.Name -eq $ProjectFolder}  
 $destFolder = $destcatalog.Folders | Where{$_.Name -eq $ProjectFolder}  
 # If the destination folder is missing - create it  
 if($destFolder -eq $null)  
   $destFolder = New-Object $ISNamespace".CatalogFolder" ($destCatalog, $curFolder.Name, $curFolder.Description)       
 #This deploys everything under the projects  
 Write-Host "Starting Project Deployments for " $curFolder.Name  
 foreach($proj in $curFolder.Projects)  
   if($con.State -eq "Closed") {$con.Open()};  
   $cmd = New-Object System.Data.SqlClient.SqlCommand  
   $cmd.CommandType = "StoredProcedure"  
   $cmd.connection = $con  
   $cmd.CommandText = "SSISDB.catalog.get_project"  
   $cmd.Parameters.Add("@folder_name",$curFolder.Name) | out-null;  
   $cmd.Parameters.Add("@project_name",$proj.Name) | out-null;  
   [byte[]]$results = $cmd.ExecuteScalar();  
   if($results -ne $null) {  
     $deployedProject = $destFolder.DeployProject($proj.Name,$results)  
     Write-Host $proj.Name $deployedProject.Status  
   else {  
     Write-Host $proj.Name " failed - no results from Get_Project"  

If you are looking to learn more, I will be presenting on this and many other similar scripts at SQL Saturday 206 in Madison, WI .  Here is a map of the campus.

1 comment: