Azure DevOps - New Microsoft SSIS Build task

Solution
Until now we needed Third Party tasks or PowerShell (with NuGet) to build an SSIS project in DevOps. Now Microsoft finally released its own SSIS DevOps tasks. How does it work?

Four tasks that will be replaced by one new task

Case
Microsoft just released SSIS Build (public preview) which makes it much easier to build an SSIS project. Below you will find the codeless steps to build and publish the artifacts so that they can be deployed.

1) Create new build pipeline
In this step we will create an empty build pipeline which is connected to Azure Repos Git where our SSIS project is located.

• Go to Pipelines menu in DevOps and then to Builds. In the list of pipelines you will find the + New sign on top. Click on it and choose New build pipeline.
• Now you can choose where your code is located. However on the bottom you will find the option "Use the classic editor to create a pipeline without YAML.". After clicking on it you must select the source you want to use for building the code (Azure Repos Git in our example).
• Next step is to create an Empty job/pipeline and give it a useful name.

Create new empy build pipeline

2) Add SSIS Build task

In this step we will add the new SSIS Build task from Microsoft. Make sure to pick the right one because there are several tasks with the same name. You want to look for "by Microsoft Corporation" or "by BeisiZhou".

• Click on the plus icon behind the "Agent job 1" (you probably want to give that one a better name) and search for "SSIS Build" choose the SSIS Build from Microsoft (see animated gif)
• Edit the new SSIS Build task. The only thing you need to change is the Project Path property. You can use the ellipsis button to browse to your SSIS project file.

Add SSIS Build task by Microsoft

3) Publish Artifact
The previous task will create an IsPac file that we need to publish so that we can use it in a release pipeline later on.

• Click on the plus icon behind the "Agent job 1" and search for "Publish artifact" choose the Publish artifact from Microsoft (see animated gif)
• The only thing you need to change is the Artifact name. Don't use the default 'drop', but use you SSIS project name instead.

Publish artifact (IsPac)

4) Add Trigger
To trigger this build pipeline automatically when changes are committed to the Master branch we need to add a trigger. Go to the triggers menu and enable continuous integration. Then add a path filter so that this build will only trigger when the specific project changes. Then save the Build pipeline and trigger it by committing a change or by hitting the trigger options for a manual trigger.

Add trigger

Conclusion
This new task is much easier to use than the PowerShell code and also easier than most of the third party tasks. With a little practice you can now easily create a build task under two minutes which is probably faster than the build itself.

If your build fails with the following error message then you are probably using a custom task or component (like Blob Storage Download Task). These tasks are not installed on the build agents hosted by Microsoft. You can either install them via PowerShell or use a self hosted agent where you can install all custom components.

System.ArgumentException: Value does not fall within the expected range.

   at Microsoft.SqlServer.Dts.Runtime.Interop.ProjectInterop.ReferencePackage(Package package, String packageLocation)
   at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events)
   at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package()
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)
ERR:The process 'C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.exe' failed with exit code 1
Build D:\a\1\s\SSIS\STG\STG_MySource\STG_MySource.dtproj failed
ERR:Build failed

##[error]Build failed