TFS SQL DACPAC Deployer With Release Management 2015

In this post I’m going to explore on how we can get the SQL Packager running on a machine that does not have Visual Studio installed. I’ll be explaining this from the context of using TFS with Release Management 2015 but it also applies to using SQL Packager in a stand alone manner.

In order to be able to run SQL Packager for SQL Server 2014 on an agent based machine a few things need to be done. 

First off we need to create our own component that contains the latest SQL Packaging program files. Essentially this is a copy of the out of the box DACPAC deployer that ships with Release Management. The key difference is to point to the new DLL’s in the ‘Resources’ section. For the Feb 2015 update the installer can be found HERE (Or simply search for: Microsoft® SQL Server® Data-Tier Application Framework).

SQL Packager

Then we simply add the component to the workflow and give it a try. Pretty good chance the following error will occur. They key part to identify in the error is: Could not load file or assembly ‘Microsoft.SqlServer.TransactSql.ScriptDom’. SQL Packager is missing a dependency: Microsoft SQL Server 2014 Transact-SQL Script Dom.

An unexpected failure occurred: The type initializer for ‘Microsoft.SqlServer.Dac.DacPackage’ threw an exception..

Unhandled Exception: System.TypeInitializationException: The type initializer for ‘Microsoft.SqlServer.Dac.DacPackage’ threw an exception. —> System.TypeInitializationException: The type initializer for ‘Microsoft.SqlServer.Dac.DacServices’ threw an exception. —> System.TypeInitializationException: The type initializer for ‘SqlSchemaModelStaticState’ threw an exception. —> System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.SqlServer.TransactSql.ScriptDom, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlCoreAnnotationRegister.RegisterModelAnnotations(ModelSchema storeSchema)
at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState.RegisterModelSchema()
at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState..cctor()
— End of inner exception stack trace —
at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState.get_ModelSchema()
at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.InitializeModelSchema()
— End of inner exception stack trace —
at Microsoft.SqlServer.Dac.DacServices.Initialize()
— End of inner exception stack trace —
at Microsoft.SqlServer.Dac.DacPackage.Load(String fileName, DacSchemaModelStorageType modelStorageType)
at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.<>c__DisplayClass1c.<GetDacPacFromDisk>b__1a()
at Microsoft.Data.Tools.Schema.CommandLineTool.ValidationUtil.SafeFileAccessWrapper(Func`1 action, Action`1 reportException)
at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.GetDacPacFromDisk(String filePath, DacSchemaModelStorageType storage, Action`1 writeError)
at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.DoDeployAction(DeployArguments parsedArgs, Action`1 writeError, Action`2 writeMessage, Action`1 writeWarning)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoDeployActions(CommandLineArguments parsedArgs)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args)

To confirm that it is indeed missing verify the presence of the following folder on the agent machine:

SQL Script DOM

SQL Server 2014 Feature Pack – Scripting Files

If the folder does not exist (which it probably should not!) then download and install the scripting components from the SQL 2014 feature pack. The latest version the bits can be found HERE (Or simply search for: Microsoft® SQL Server® 2014 Feature Pack). The feature pack has many MSI’s bundled in it and the one you are looking for is: ENU\x64\SqlDom.msi.

Now when you use your TFS SQL DACPAC deployer from Release Management it should process without any issues.

Thanks for following along…

Neil Moffatt

Passionate about everything ALM and agile process/tools adoption into regulated organizations (FDA). Specializing in medical device companies.

More Posts - Website

Follow Me:
TwitterLinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *