dacpac post deployment script not running

Possibly save 1 hour of your time: In your database project, you may set it to build as a dacpac. Then you can use this dacpac to deploy scripts to your database. However, after you setup a post deployment script, it still didn’t seem to execute after you deploy the dacpac to your target database. Here are some guidance:

  1. If you haven’t already, you can add post deployment script by right click any folder in your project and choose Add >> Script. This script should contain your script or point to another script using notation :r .\myfile.sql. Use doubt quotes to wrap file path/name with space.
  2. After add script, right click the file and ensure Build Action is set to PostDeploy.
  3. Now build the project.
  4. Go to bin folder and find the dacpac. You can right click on this file and unpack it somewhere. Once unpacked, please verify there is a file name postdeploy.sql with the script code inside. This should be it you script will be execute when you deploy your dacpac. Make sure you are targeting the right database and verify using the right database. I got burned once looking at a different staging database.

Command for deploying dacpac:

“C:\Program Files\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe” /Action:Publish /SourceFile:”Your Database.dacpac” /TargetDatabaseName:YourDatabaseName /TargetServerName:localhost /p:ExcludeObjectTypes=”ApplicationRoles;DatabaseRoles;Permissions;RoleMembership;Users;Credentials;ServerRoleMembership;ServerRoles” /p:BlockOnPossibleDataLoss=False

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s