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:

create certificate [YourCertificateName]
authorization [dbo]
with
subject = N'Some description about your encryption certificate'
, start_date = N'01/01/2012 00:00:00'
, expiry_date = N'12/31/2050 00:00:00'
active for begin_dialog = on;

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 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:

create certificate [YourCertificateName]
authorization [dbo]
with
subject = N'Some description about your encryption certificate'
, start_date = N'2012-01-01T00:00:00'
, expiry_date = N'2050-12-31T00:00:00'
active for begin_dialog = on;

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

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store