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

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

Eugene Niemand

Written by

Lead Data QA 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

Eugene Niemand

Written by

Lead Data QA 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

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

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