SSMS: DACPAC pre-deployment and post-deployment scripts fail to execute in the "Deploy Data-tier Application" wizard for initial DACPAC deployments - by steve-jansen

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 737150 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 4/16/2012 10:26:48 AM
Access Restriction Public


The "Deploy Data-tier Application" wizard for Sql Server 2008 R2 SP1 Managment Studio (SSMS) fails to execute the pre-deployment and post-deployment scripts when deploying a new data tier application (i.e., ".dacpac" file).  This problem does not affect the "Upgrade Data-tier application" wizard in SSMS.  

Pre- and post- deployment scripts remain critical to configuration management of category/seed data for a database. The DACPAC file format does not support DML for category/seed data outside of the pre-/post- deployment steps, making the pre-/post- steps critical for successful deployments of category/seed data.  Improper configuration managment of category/seed data can result in broken deployments of applications dependent on SQL Server databases.

There are three known workarounds for this issue, however, two of the workarounds invalidate the expected single file, single wizard workflow of DACPAC deployments.  The other workaround requires Visual Studio for database deployments, which is imperfect for deployments to non-developer environments such as production.

As reference, the following components were used to reproduce the problem on a single machine:

Windows 7 SP1 32 bit - v6.1.7601
Microsoft .NET Framework - v4.0.30319.261
SQL Server 2008 R2 Management Studio - v10.50.2500.0
Microsoft SQL Server 2008 R2 (SP1) Express - 10.50.2500.0 (Intel X86)
Microsoft.SqlServer.Management.SqlParser.dll - 10.50.2500.0
Microsoft.SqlServer.Management.Dac.dll - 10.50.2500.0
Microsoft.SqlServer.Management.DacEnum.dll - 10.50.2500.0

There are three known workarounds:

1) Redeploy the same .dacpac file using SSMS's "Upgrade Data-tier application..." wizard under the [Server]/Management/Data-tier Application/[Application Name] node in the SSMS Object Explorer.  The upgrade wizard presents presents checkbox options to execute the pre- and/or post-deployment scripts in the .dacpac file.  Enabling both the pre- and post-deployment script options, then performing the upgrade will produce the expected result.

2) Manually execute the DACPAC pre- and/or post-deployment T-SQL script files using a SSMS query window, SQLCMD.exe, or simliar.  This requires the DACPAC author to ship the pre-/post-deployment scripts alongside the DACPAC file.  Alternatively, the pre-/post-deployment scripts can be extracted using the Microsoft DacUnpack.exe utility, or a ZIP file utility (after renaming the file extension from .dacpac to .zip).

3) Use either MSBuild.exe (v4.0.30319.1, or higher) or Visual Studio 2010 Premium SP1 (or higher) to deploy the "SQL Server Data-tier Application" project file.  (Exampe: "msbuild /Target:Deploy DeploymentDemo.dbproj").
Sign in to post a comment.
Posted by Microsoft on 1/24/2013 at 7:27 PM
Hi!, thanks for writing in to Microsoft.

We took a look at this bug and triaged it against several others and unfortunately, it did not meet the bar to be fixed. While we are closing this bug as 'won't fix', we have taken note of this internally and we will try & incorporate this request when we revisit this functionality in a future release of SSMS.

Meanwhile, we suggest that one of the workarounds in this connect bug thread be considered.

Sanjay Nagamangalam, SQL Server Manageability