Release Management for Visual Studio 2013 and SQL Server 2014 Database Deployments


Visual Studio 2013 comes with built-in support to manage database projects, the SQL Server Data Tools (SSDT) were updated in March 2014 to include support for SQL Server 2014.  If you have Visual Studio 2012 use the SQL | Check for Updates inside Visual Studio or download. If you have Visual Studio 2013 use Tools | Extensions and Updates | Updates for the latest update.

Don’t confuse the SSDT with the SSDT-BI, if you are looking for the SQL Server Data Tools Business Intelligence (SSDT-BI) tooling for Visual Studio 2013 you can find it here.  SSDT-BI installs project templates for Analysis Services, Integration Services, and Reporting Services.

If you attempt to use the DACPAC Database Deployer tool that is available in Release Management  to publish .dacpac to a SQL Server 2014 instance you will receive the following error which you can see by viewing the command output:

 

Deployment Log - DACPAC Database Deployer

Deployment Log – DACPAC Database Deployer

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.

From within Release Management, if you navigate to Inventory | Tools | DACPAC Database Deployer

DACPAC Database Deployer

DACPAC Database Deployer

You can see in the lower portion of the window, the executable and assemblies that are being deployed to the Release Management Deployment Agent, these files are not compatible with SQL Server 2014.  The great thing is this is super easy to fix (assuming you have the latest DAC files).

Click Close, then Click New

 

Inventory | Tools | New

Inventory | Tools | New

You can find the latest files for DAC in either of the following folder(s) or both, depending on your configuration.

  • C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin
  • C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120

Here is how I configured the *new* DACPAC Database Deployment Tool:

*new* DACPAC Database Deployer

*new* DACPAC Database Deployer

All of the files you see under resources were added from “C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin” .in the above example.  Once you have done that you can update your component that is being used by your Release Template to deploy the database by selecting the updated DACPAC Database Deployer v12 as the Tool in the Deployment tab.

DACPAC Database Deployer v12

DACPAC Database Deployer v12

 

Now you should have no issues publishing your databases to your various environments.  Here is an example of my environment where I deployed to DEV and QAT.

Successful Deployment of .dacpac using the RM Deployment Agent

Successful Deployment of .dacpac using the RM Deployment Agent

If you have any questions feel free to post them below.  Happy Releasing!

About wesmacdonald

Wes MacDonald is a Visual Studio ALM MVP, PSD, MCT and a Principal Consultant for LIKE 10 INC., a SharePoint Server, Visual Studio and Team Foundation Server Consulting Firm providing premium support and guidance services.

9 Responses to “Release Management for Visual Studio 2013 and SQL Server 2014 Database Deployments”

  1. Very useful post (for me, of course, as I’ve had just this problem). Your fix fixed the original problem however only to clear the way for a further problem – the new tool’s Resources for some reason throw up an exception, specifically a TypeLoadException on Microsoft.SqlServer.Dac.ObjectType, so it seems that the copied version of sqlpackage.exe can’t load the transferred version of Microsoft.SqlServer.Dac.dll and the release is rejected. These files come directly from my install folders, which contain the right versions, and I’ve even tried running the command on the target machine (at C:\Windows\Temp\RM\DeployerTools\..etc) and this works!… just not via a proper release. Any idea where I’m going wrong here?

    • Hi,

      It is possible the wrong version is being copied by Release Management. Log on to the remote machine and fine the directory where the tool is installed and verify the timestamps of the files (they should all be the same) then try and run it. In my case it exists here:

      C:\Users\inrDeployer\AppData\Local\Temp\ReleaseManagement\DeployerTools\2028\2

      Regards,
      Wes
      Wes

  2. Tried this and it didn’t work for me. I can run a command line from my local box and get it to deploy a dacpac successfully, but if I try to use Release Manager with a properly configured v12 tool, it still fails with “Could not load file or assembly ‘Microsoft.SqlServer.TransactSql.ScriptDom, Version=12.0.0.0…”. I even ran the fix suggested by this link, “https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4564ef7a-9175-4349-8129-5d63ba9a65ee/new-version-still-missing-things?forum=ssdt”, on my release machine and it still fails.

    • Hi,

      In my blog post the command is run by the RM Deployer Agent on a remote SQL Server 2014 virtual machine. Looking right now my Add/Remove Programs on that machine reports two versions of Microsoft SQL Server 2014 Transact-SQL ScriptDom versions are 12.0.2000.8 and 12.0.2430. The earlier version corresponds with the date used when the blog post was written. Here is the feature pack associated with the RTM release which has those X86 and x64 Package(SQLDOM.MSI):

      https://www.microsoft.com/en-ca/download/details.aspx?id=42295

      Have you validated the command runs on the same machine the agent is trying to execute it? You should fine the tool deployed to a directory like the following: (in my case the user account running the Deployment Agent is a domain account named inrDeployer).

      C:\Users\inrDeployer\AppData\Local\Temp\ReleaseManagement\DeployerTools\2028\2

      Try running sqlpackage.exe from that folder.

      If you are using/applied SQL Server 2014 SP1, make sure you also get the latest Feature Pack.

      Regards,
      Wes

  3. How can I add multiple users to the same database instance via this process in release management?
    PERERAP@YAHOO.COM

  4. Thank you so much for these directions.
    I had been struggling with this issue for the last 12 hours, installed SSDT, DAC Framework and the other 2 components, but couldn’t get around this.
    These instructions helped me resolve the exit code 255 issue and deployed the DB successfully.

  5. Hi – I wanted to note here that I has persistent errors with the ScriptDom.dll v12 message others have referenced above. After creating the new Release Management tool for “DACPAC Database Deployer v12” as you suggest, I was finally able to make it work by additionally adding the Microsoft.SqlServer.TransactSql.ScriptDom.dll to the tool as well. In my case, the other files like Microsoft.SqlServer.Dac.dll were only in the Visual Studio directory — the ScriptDom file was in the SQL Sever directory tree.

  6. Hi – I wanted to note here that I had persistent errors with the ScriptDom.dll v12 message others have referenced above. After creating the new Release Management tool for “DACPAC Database Deployer v12” as you suggest, I was finally able to make it work by additionally adding the Microsoft.SqlServer.TransactSql.ScriptDom.dll to the tool as well. In my case, the other files like Microsoft.SqlServer.Dac.dll were only in the Visual Studio directory — the ScriptDom file was in the SQL Sever directory tree.

Trackbacks/Pingbacks

  1. Apexia software - June 16, 2015

    […] The standard version of the DACPAC Database Deployer doesn’t match with the current version of SQL Server (2014 at time of writing). This is annoying, but easily fixed. Simply create a new DACPAC Database Deployer compatible with SS2014 (v12) with the same details but one extra library – ‘Microsoft.SqlServer.Dac.Extensions.dll’, and use this Tool in deployment component. Source here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: