SQLPackage and SSDT dropping and recreating items where they already exist and no changes were made

Eugene Niemand
Jul 29, 2013 · 2 min read

So I have been struggling for hours trying to figure out why my SQLPackage.exe task thinks it needs drop my Certificates even though I just deployed them and haven’t made any changes. Look at the example below, this is not the SSDT project or output it is only used for illustrating the problem and solution:

If you run this from SSDT or SQLPackage where you have the above Certificate scripted as part of a Database Project and deploy this more than once you will notice that it is dropping and recreating the Certificate every time. This happens without you making any changes and the object already existing. My colleague Simon Sabin point out the following “The reason for this is that the dates in the above script aren’t ISO Standard.” This means that if you run a Schema Comparison you will notice the Certificate on the server looks like this:

When a date is stored as a property or into a Date column it is converted to ISO standards and thus when you script an object using a different format they will look different even though they are the same and cause the object to be dropped and recreated.

Eugene Niemand’s Blog

Technology and Programming professional and enthusist

Eugene Niemand

Written by

Senior Data Engineer at ASOS.com - I have a passion for Test Driven Development, Agile Methodologies, Continuous Integration and Delivery using Microsoft Azure

Eugene Niemand’s Blog

Technology and Programming professional and enthusist