Publishing database changes with SSDT that include altering data types of columns I have a SQL Server Data Tools (VS2012) project that's published automatically during the build process. A column was recently updated from an int to decimal(18,4). As a result of this change, the publishing fails with the error 


  (49,1): 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. (44,0): SQL72045: Script execution
  error.  The executed script: /* The type for column QuantityReceived
  in table [dbo].[Reconciliation_Receiving] is currently  INT NOT NULL
  but is being changed to  DECIMAL (18, 4) NOT NULL. Data loss could
  occur.
  */
  
  IF EXISTS (select top 1 1 from [dbo].[Reconciliation_Receiving])
      RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT An error
  occurred while the batch was being executed.


I understand why I'm receiving that error, and I know it could be resolved by disabling the "Block Incremental Deploy if Data Loss May Occur" flag. However, there's very strong opposition to disabling that feature, so it's not going to be an acceptable solution.

The only other solution I can think of is to do the following:


Make a temporary table and copy the existing table's contents into the temporary table
Truncate the existing table
Let SSDT update the data type
Fill the data back in from the temporary table


That seems horribly clunky and inefficient, though. 

Is there a better alternative?
I've been tempted to bypass that flag also but have come down on the side of your co-workers and now try to deal with these issues "correctly". The (marginally) less clunky route is to use pre and post deployment scripts to do the work with a rename.


Rename the existing table in a pre-deployment script.
With the existing table missing the table in focus will be created as per the new schema definition.
In a post-deployment script copy from the renamed original table to the new version.


Depending on the nature of the target you may of course need to take care of dropping and recreating foreign key constraints.