So I have a SQL database project which tracks our application's database schemas, stored procedures, etc. It also has deployment script generation sections, including pre- and post-deployment scripts. Part of these scripts includes doing things like caching values of columns that have been removed so they can be moved elsewhere (usually normalized into its own table).
The problem is that the upgrade script process I have implemented, which automatically detects the version and what updates need to be applied at deploy-time, includes references to columns which no longer exist. This is fine when deploying to either an empty database (so the script executor doesn't know there's no column) or to a being-upgraded database, as it can see the column before the deployment. The script validator however doesn't like that I'm referencing columns from a table that it knows about, even if the section which references it will never get called because of the conditions around it.
My current solution is to store in a string all of the parts of the script which handle columns being removed, then running those sections dynamically using EXEC. This is a little icky to me (and EXEC is a code smell issue), though not the worst thing. This is code that's only run once per DB and is never accessible during production operation.
What I want to know is, is there a better way of doing this? Can I somehow comment to the validator that "This won't run, please ignore it"? Is this whole process terrible? (I'm not that experienced of a DB admin at all, so any expertise here would be very helpful.) If I'm doing this all wrong, how do you handle one-off scripts for database updates? I've also considered doing one-off conditional compilations using SQLCMD declarations/labels, then automatically removing sections that aren't needed for a particular version-to-version upgrade using a script.
As a last note, we are using SQLPACKAGE to generate our upgrade scripts. Is there possibly a way to tell the tool to omit some sections in certain cases?