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").