To help with this scenario (it is pretty specific):

You have a table that has columns that you do not know about when you create the dacpac, possibly you have a customer who modifies your schema after you deploy. If you deploy your dacpac those columns will be dropped which is bad and you don’t want to completely ignore all changes to a table what do you do?

You can now use the new version of my deployment contributor with a filter like SqlPackageFilter=KeepTableColumns(Employees)

This stops the DacFx from dropping any columns on Employees or rebuilding the Employees table which would cause an implicit drop of the table.

There are some things to know about this:

• 1. This will stop ALTER TABLE DROP COLUMN statements
• 2. It won’t drop ALTER TABLE ADD COLUMN statements
• 3. If you try to do something that would cause a table rebuild that is cancelled
• 4. I have made some assumptions about the code which I am fairly sure about but it is undocumented so there is an additional risk

What I would suggest is actually what I suggest anyway, use the same dacpac for different environments and use the same processes and test your deployment scripts before releasing to production / customers.

If you have any questions or any issues please shout!

---

Comments:

Markus

August 17, 2016 - 12:17

Hi Ed,

Hi Ed,

any chance we can adjust the behaviour to allow ADD COLUMN will simply happen in case it comes along with a difference in column order would end in a table rebuild?
Right now i some columns are ordered differently in the dacpac than in destination the rebuild of the table is cancelled entirely (which is good) but any added columns will not be added in this case (which is bad).

Markus

Ed Elliott

August 17, 2016 - 14:28

HI Markus,

HI Markus,

At the moment it isn’t easy to find out why a change is happening - I know the SSDT team are working on fixing the column order issue but it seems to be taking a while!

ed

Nathan Latton

October 20, 2016 - 03:49

Question

Hi Ed,

Your tool looks to be what I’m after. I’ve given it a spin, but unfortunately it didn’t seem to work. Std Out included these lines:

Starting AgileSqlClub.DeploymentFilterContributor
Step removed from deployment by SqlPackageFilter, object: [dbo].[CRTCARDS], step type: Alter

unfortunately, std err ended with these lines (calling SqlPackage.exe from the command line):

Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT TOP 1 1
FROM [dbo].[CRTCARDS])
RAISERROR (N’Rows were detected. The schema update is terminating because data loss might occur.’, 16, 127)
WITH NOWAIT;

So it looks like the filter hasn’t “stuck”?

I can send you more info (full logging, command line parameters) if you have time to look into this.

Thanks, Nathan

Jonathan

March 27, 2017 - 21:13

RE: Question

Hi Nathan / Ed, I am seeing the same issue myself, did you ever get to the bottom of what was going on?

Jonathan

Ed Elliott

March 28, 2017 - 15:39

YES! someone submitted a

YES! someone submitted a change to also remove the check for data when we pull a table out, have you got the latest version of the contributor?

https://agilesqlclub.codeplex.com/SourceControl/latest#SqlPackageFilter/…

https://agilesqlclub.codeplex.com/downloads/get/1600766

ed

Jonathan

March 28, 2017 - 22:19

Hi Ed,

Hi Ed,

Yes, downloaded the latest release (1.4.4)

Jonathan