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,"")
$f.Create()
}
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)"
}
else
{
write-error
"Deploying $($results.ObjectName) returned
with a status of $($results.Status): $($results.Messages)"
}
}
There are two steps that happen here:
- Create an SSIS folder to deploy to
- 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: continuous deployment, Continuous Integration, Powershell, SSIS