Microsoft Database Project - How to change column type and avoid data loss error

Question

I am trying to change the type of a column from VARCHAR to INT. During the deployment, the database project will stop the deployment due to the "data loss" error:

RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)

I know the data is convertible and if I run a manual ALTER TABLE script it will be fine. However, I cannot integrate that properly with this scenario to avoid the error during the deployment.

1. What is your solution to resolve my problem?
2. Is there a method to override this behaviour in a database project and for this particular case, use a custom script?

Answer 1

One way in such scenario is using PreDeployment script and deploy twice.

1. Change data type column in table definition as usual

2. Add in Predeploy script:

    -- this script has to be idempotent, and removed after some time
    IF EXISTS (SELECT 1
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'table_name' 
                 AND TABLE_SCHEMA = 'schema_name'
                 AND COLUMN_NAME = 'column_name
                 AND DATA_TYPE != 'INT'  
               ) 
    BEGIN
       ALTER TABLE schema_name.table_name ALTER COLUMN Column_name INT NULL/NOT NULL;
    END 

First publish will change the data type during PreDeploy, and deploy will fail with Potential Data loss error.

Second publish will omit the part of PreDeploy(if condition), and schema compare does not detect any changes, meaning it has been changed.

Next step should be removing the manual part from PreDeployment script.