Automating Deployment of SSIS - 2) Publishing the Project

This post follows on the heels of building an SSIS Project.

Continuous Integration leaves us with a working set of code, but it does not necessarily deploy it.  When we get into serious automated deployment, we need to start looking at Continuous Delivery (CD) servers.  This could be Release Management from Microsoft, Octopus Deploy, Maven or one of many others.  There will be no hooks for easy SSIS deployment in any of these so we will script this out in powershell.

Here is an example script, which I will follow with some explanation:

function createSsisFolder([string]$folderName)
        if ($catalog.Folders[$folderName] -eq $null)
                write-host "Creating the folder $folderName..."
                $f = new-object "$ISNamespace.CatalogFolder" ($catalog, $folderName,"")
        Return $catalog.Folders[$folderName]


    Main Script


#load the integration services library
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$SSIS_server = "localhost\sql2014"
$sqlConnectionString = "Data Source=" + $SSIS_server + ";Initial Catalog=master;Integrated Security=SSPI;"

#create a SQL Connection object
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
#Connect to SSIS
$integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection
#Get an instance of the local catalog
$catalog = $integrationServices.Catalogs["SSISDB"]

#creates a folder in the catalog called "ETL" and returns the object
$folder = createSsisFolder "ETL"
$SSIS_Projects = get-childitem "c:\<Staging folder for projects during deployment>\*" -include *.ispac -recurse

foreach ($SSIS_Project in $SSIS_Projects)
        write-host "Deploy $($ssis_Project.ToString())"

        #load the project into a byte array
        [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($SSIS_Project.FullName)

        #projects are deployed from the folder with a method called DeployProject
        $results = $folder.DeployProject($SSIS_Project.BaseName, $projectFile)
        if ($results.Status -eq "Success")
                write-host "Deploying $($results.ObjectName) returned with a status of $($results.Status)"
                write-error "Deploying $($results.ObjectName) returned with a status of $($results.Status): $($results.Messages)"

There are two steps that happen here:

  1. Create an SSIS folder to deploy to
  2. Deploy all of the projects from a staging directory to the the SSIS folder

We use the Microsoft.SqlServer.Management.IntegrationServices namespace for deployment.  The beginning of the script declares a function for creating the folder for cleaner code.  All the comments would be stripped out of the final script as well.  Once the catalog folder is created, we can use the DeployProject method on the folder.  This method accepts the name of the project (without the extension) and the contents of the file in a byte array.

Since the deploy project output does not give the deployment server a very clear outcome, we check the status of the results and throw an error if there was an issue.

Next up, Creating an Environment...

Labels: , , ,