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:
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=220.127.116.11, 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
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
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:
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.
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.
If you have any questions feel free to post them below. Happy Releasing!