Three ways to build SSIS projects in VSTS
29 mei 2018 2021-10-13 8:40Three ways to build SSIS projects in VSTS
When setting up Continuous Integration in VSTS, chances are you’ll run into the problem of building SSIS projects: where database projects are building just fine, SSIS just doesn’t build out-of-the-box (at least not at the time of writing). Here’s how you fix it:
First things first
First, set your expectations: you won’t create a one-size-fits-all build task that will build all your project types. Instead, you will split up your builds by project type - essentially just as described in Continuous Integration for BI in VSTS: Splitting Build Steps by Project Type.
Building SSIS projects
With folder and solution structure in place, we'll explore three ways to build SSIS projects:
- SSISBuild / SSISDeploy
- Just-for-build SSIS projects
- "Build" inside PowerShell
SSISBuild / SSISDeploy
One way to build your SSIS projects is using SSISBuild. SSISBuild is a program available from NuGet[ref]https://www.nuget.org/packages/SSISBuild/[/ref], which makes it very easy to include at any agent you spin up, either in Azure or self-hosted. It basically provides a native build step doing a SSIS Build step. In order to use SSISBuild[ref]I don't use the accompanying SSISDeploy tooling, because how to do SSIS Deployment from within PowerShell is pretty well documented[/ref], I take the following build steps:
- Install NuGet
- Use NuGet to install SSISBuild
- Call on a PowerShell script to build all .dtproj files
The PowerShell script just loops through my designated SSIS project folders looking for .dtproj
files, and calls on SSISBuild
to do the compilation[ref]The $($env:XXX) are references to VSTS environment variables, which help me locate where SSIS Build is stored, and what my current Release Configuration is[/ref]:
Get-ChildItem -Filter *.dtproj -Recurse | Where-Object { -Not ($_.FullName -match "obj") } | ForEach-Object { &"$($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.1.2\tools\ssisbuild.exe" $_.FullName -Configuration $($env:ReleaseConfiguration) -ProtectionLevel DontSaveSensitive if ($LASTEXITCODE -ne 0) { throw "Build failed."; } }
There are two caveats with this method:
- SSISBuild hasn’t been updated for over 1.5 year. This hasn’t been a problem (it just works), but it doesn’t feel like a safe bet. Don't worry too much about this: the code is out in the open (Apache license), and it really is pretty clear structured - you can check it out on Roman Tumaykin's GitHub.
- You need to check-in the “.dtproj.user” file into your VCS - by default, these are ignored by Git and TFS.
Just-for-build SSIS Projects
As an alternative, you can hand-craft a solution which will help you build SSIS projects with MSBuild, like described in VSTS Continuous MSBuild for DWH / BI.
"Build" inside Powershell
A third approach is to create your own build tooling. After all, remember that an .ispac-file is just a zip, containing the following files:
- [Content_types].xml[ref]Really. Whoever did think up of requiring a file with square brackets in the filename?[/ref]
- One or more SSIS packages
- A parameters file
- A project manifest
- Zero or more connection managers
So a very simple initial approach could be like this (Powershell for Windows):
$dtProjFolderToLoad = 'C:\My\Path\To\MySSISProject' $dtProjFileToLoad = Join-Path $dtProjFolderToLoad 'MySSISProject.dtproj' [xml]$dtProjXmlDoc = New-Object System.Xml.XmlDocument $dtProjXmlDoc.PreserveWhitespace = $true $dtProjXmlDoc.Load($dtProjFileToLoad) # Create folder with the project name. This will essentially be zipped into an ispac $ispacFolder = 'C:\My\Path\To\MySSISProject\bin\CustomRelease\MySSISProject' New-Item -ItemType Directory -Force -Path $ispacFolder # Create the project manifest in the ispac folder # Exists in node /Project/DeploymentModelSpecificContent/Manifest/SSIS:Project $projectManifestXml = $dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.OuterXml $projectManifestFullPath = Join-Path $ispacFolder "@Project.manifest" $projectManifestXml | Out-File $projectManifestFullPath -NoNewline # Add [Content types].xml, which has a static content $contentTypesXml = "<?xml version=`"1.0`" encoding=`"utf-8`"?><Types xmlns=`"http://schemas.openxmlformats.org/package/2006/content-types`"><Default Extension=`"dtsx`" ContentType=`"text/xml`" /><Default Extension=`"conmgr`" ContentType=`"text/xml`" /><Default Extension=`"params`" ContentType=`"text/xml`" /><Default Extension=`"manifest`" ContentType=`"text/xml`" /></Types>" $contentTypesFullPath = Join-Path $ispacFolder '[Content_Types].xml' $contentTypesXml | Out-File -LiteralPath $contentTypesFullPath -NoNewline -Encoding "UTF8" # Iterate over all SSIS packages (*.dtsx) inside the .dtproj file add them to the ispac folder $dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.Packages.Package | ForEach-Object { $fileToCopy = (Join-Path $dtProjFolderToLoad ([string]$_.Name)) Copy-Item $fileToCopy $ispacFolder } # Iterate over all project-level connection managers (*.connmgr), add them to the ispac folder $dtProjXmlDoc.Project.DeploymentModelSpecificContent.Manifest.Project.ConnectionManagers.ConnectionManager | ForEach-Object { $fileToCopy = (Join-Path $dtProjFolderToLoad ([string]$_.Name)) Copy-Item $fileToCopy $ispacFolder } # Copy the parameters file to the ispac folder $paramsFullPathSource = Join-Path $dtProjFolderToLoad "Project.params" Copy-Item $paramsFullPathSource $ispacFolder # Archive the ispac folder as a ".ispac" file Compress-Archive ($ispacFolder + "\*") ($ispacFolder + ".zip") -Force Rename-Item ($ispacFolder + ".zip") ($ispacFolder + ".ispac") -Force #Remove-Item $ispacFolder -Recurse -Force
This example is pretty basic: for example, it lacks support for password-protected projects, and doesn't check for Windows line-endings. It will suffice for most SSIS projects I'm currently using with my customers though. It's also way easier to maintain for a BI developer than a separately compiled executable.
Wrapping up
I've shown you three ways of building your SSIS projects inside VSTS:
- using external tooling (like SSISBuild)
- creating a custom project file for MSBuild
- creating your own build logic to generate your .ispac-file.
For me, it was quite a revelation to realise I don't have to use the Microsoft libraries to "build" my ispac files, but can easily create them myself - basically because there is no real "building" going on. I'm pretty curious which method you prefer (as long as there's no "out-of-the-box" support for SSIS projects, of course), so by all means send me a message about your experiences. If you run into problems, don't hesitate to contact me as well!
Want to learn more?
If you'd like to learn more, I'll be speaking about this subject at the Microsoft Advanced Analytics Usergroup (Mechelen, Belgium). For a more in-depth look, I've created a course about setting up CI / CD using VSTS in Microsoft BI environments. For more information about the course check:
Comments (5)
Ken
Great article Koos - Question on custom built in logic under PowerShell.
Custom powershell script did create *.ISPAC file however it's bigger than the actual ISPAC file created when build the project from Visual Studio. Any comments or anything i would need to know? This seem to be easiest solution and want it to work 🙂
Koos van Strien
Hi Ken, thanks for commenting!
The size of the ISPAC file is largely dependent on the exact compression algorithm and settings. If you want to be sure that your "hand-crafted" ISPAC is the same as the one Visual Studio produces, you should unzip them both, and then run a WinMerge across both folders to note the differences. If the contents are the same, the size doesn't mean that much.
Joel Greijer
Awesome, thanks for the nice article.
I had some problems with cryptic errors until i forced the [Content_types] as UTF-8.
$contentTypesXml | Out-File -LiteralPath $contentTypesFullPath -NoNewline -Encoding "UTF8"
/joel
Koos van Strien
Thanks for the addition - I've added it right away!
🖖
Gaspard
Hi Koos,
This method won't work with packages created/updated with BIML. The created manifest file doesn't take over all the existing package parameters when there is no deployment done. When looking in the created Ispac File some of the (new created) parameters are missing in the manifest file. Also they are missing in the .dtproj file (not so strange when this is the source used in this method).
Comments are closed.