DevOps : master.dacpac does not exist error message in VSTS (part V)

One of the problems I had with automated builds was the database reference in the .sqlproj file. I noticed that the master- and the msdb dacpac was referenced to a location on the C: drive. This works fine in the situation where you're the only developer, but when working together in a team and even more when you use VSTS as your build environment, it is not a good practice. If you try to build in VSTS when the dacpac has a standard location, you'll get a message like this:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\
IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac" 
does not exist.

To get rid of this error message you have to include the master.dacpac (and the msdb.dacpac) file (!) in your project (with location option, add database reference) and so I did...

But building the project in VSTS kept on giving this error. Investigating the .sqlproj file showed me that the reference was still there, even when I deleted the database reference in the solution explorer.

I finally manually removed the entries in the .sqlproj file and the problems were away...

       
<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\
 Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac">
  <HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\master.dacpac</HintPath>
</ArtifactReference>
<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\
    Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\msdb.dacpac">
      <HintPath>$(DacPacRootPath)\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SqlSchemas\msdb.dacpac</HintPath>
</ArtifactReference>   

Hennie

DevOps : Building your SSIS project in VSTS (Part IV)

Introduction

In previous blogposts, I've explained how to build a database project (Part I), how to release a database project (Part II) and how to use pre- and post deployment script (Part III) in an automated build and release process with VSTS. The next step is to examine how to build and release a SSIS project. This blogpost is the result of this exploration.

Building code in a database project is done with MSBuild. It is not possible to use MSBuild for building a SSIS project (as far as I know). The options I've have found so far are :

• Using command line task with devenv.com. 
• Download a custom SSIS task from the marketplace.

I'll examine both ways but will describe the first one in this blogpost. I've used the following sites as an inspiration : http://abelsquidhead.com/index.php/2016/10/06/build-and-deploy-ssis-packages-using-vsts/

Creating a SSIS project in a solution

Now the first step in this demo is creating a SSIS project in my WorldWideImporters (I think I mixed up the name;-) ) solution. The database project is still present. It is the same project as I described in my previous blogposts.

The next step is to build the SSIS project. In the following step the changes are committed and pushed to the Git repository.

The first step is checking whether the build and release is still working and it is. But now we have to change the build process in VSTS. We not only have to build a database project but also the SSIS project now.

Build the SSIS project in VSTS

The first step is changing the VSTS build definition in VSTS. I've made a copy of the build definition and added some tasks.

Variables
I've added two variables for the build configuration and one for the SSIS project. I did not configure the configuration properties of the properties. I just took the defaults.

Build task
The Build task is a command line task in VSTS and there are two version 1.* and 2.*. I took the 1.* version.

This example is based on the "Hosted" agent queue

       
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.com
 

Using the "Hosted VS2017" agent has another location of the devenv.com

       
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.com

These are the arguments I used for the build.

"$(Build.SourcesDirectory)\WorldWideImporters-SSDT\WorldWideImporters-SSDT.sln"  
/rebuild $(BuildConfigurationSSIS) 
/project "$(Build.SourcesDirectory)\SSISWorldWideImporters-SSDT\SSISWorldWideImporters-SSDT.dtproj"

Copy task

Use the copy task again as I described in the database build blogpost.

Publish artifact

Publish the artifact to the Artifact repository.

You can find these artifiacts in the overall result screen of the build. These will be used in the release processstep.

Conclusion

Once you understand the folder structure and using the variables it is easy to write the script to build the SSIS project with Devenv.com.

Regards,

Hennie