SQL Change Automation project settings
Published 20 August 2019
The SQL Change Automation project file is a SQL Server Data Tools (SSDT) .sqlproj project file which contains settings specific to SQL Change Automation.
These settings can be taken into account both during development in SQL Server Management Studio (SSMS) or Visual Studio and during deployment in a continuous integration environment.
You can edit some of these in Visual Studio on the Project Settings and Debug tabs in the project properties.
You can configure the following settings:
| Setting | Default value | Description |
|---|---|---|
| DevelopmentSource | Database | Where changes to the database schema are made. A development source represents the current state of the database in your development environment. |
| DSP | Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider | SQL Server version that scripts need to be compatible with. This will be derived from the target database if baselining is performed. |
| MigrationLogSchemaName | dbo | Schema where SQL Change Automation tables will be stored. You can configure this if using partial projects. |
| AdditionalScriptFolders | {empty} | When set, folders in which Additional scripts are located (for example, Additional Scripts). |
| Baselining | None | This will be set to FolderBased if baselining is performed in the UI, and None if baselining is not performed. When set to FolderBased all scripts in the first folder (according to deployment order) will be treated as baseline scripts. |
| Provisioner | Default | The provisioning strategy used by the project. |
| ProgrammableObjectHandling | UseRepeatableScriptsForAllObjects | How to script out programmable objects. See here a full description of the options. This option replaces the old DeployChangesForProgrammableObjects setting: the new ScriptInMigrations value is equivalent to False, and the new UseRepeatableScriptsForAllObjects value is equivalent to True. |
| DeployChangesExecutionOrder | {empty} | The order in which Programmable objects should be deployed. It is recommended to only alter this via the UI if the automatic ordering is insufficient. |
| DeployChangesImportObjectTypes | DdlTrigger;StoredProcedure;Function;View | The types of object to be treated as Programmable objects. |
| DeployChangesImportSchemaFolders | True | Whether to write out Programmable Object files under folders organised by schema and then by object type or whether to only arrange them by object type and include the schema in the file name. |
| FailBuildOnParseErrors | False | Enabling this option will provide extra protection for constraint naming validation and script containment during verify and build processes in SQL Change Automation. The default setting will provide warnings for these conditions. |
| SyncToOfflineSchemaModelBySchema | True | Whether to write out offline schema model files under folders organised by schema and then by object type or whether to only arrange them by object type and include the schema in the file name. |
| SyncToOfflineSchemaModelObjectTypes | Table;Assembly;Contract;Default;EventNotification;FullTextCatalog ;FullTextStoplist;MessageType;PartitionFunction;PartitionScheme; Queue;ServiceBinding;Role;Route;Rule;Schema;SearchPropertyList; Service;Sequence;Synonym;User;UserDefinedType;XmlSchemaCollection | The types of object to be written out to the offline schema model. |
| DefaultTransactionHandlingMode | Auto | Whether all Migration Scripts and Programmable objects should be wrapped in a transaction by default. The alternative value is Custom for disabling transactions by default. |
| SqlCmdVariable | {none} | SqlCmd variables to include in scripts executed against SqlCmd. A new node should be added for each new variable. (Note that this is an SSDT property rather than SQL Change Automation). |
| DatabaseObjectFilterPath | $(MSBuildProjectDirectory)\Filter.scpf | Absolute file path to a .scpf filter file (MsBuild variables can be used in the path). If this is customized the filter file must exist or an error will be raised. |
| GenerateSqlPackage | False | Generates a package script during Visual Studio build. |
| BuildOptionSkipShadowDeployVS | True | When set, SQL Change Automation does not deploy the project to the shadow database when performing a build in Visual Studio. This setting has no effect in SQL Server Management Studio. |
| BuildOptionSkipThreePartNameCheck | False | This disables the validation check that ensures that all three part names within a script (for example, SELECT * FROM [Database1].[dbo].[Test]) refer to the database being deployed to, enforcing database containment. |
| EvaluateExecutionOrderOnImport | True | Indicates that the Execution order should be evaluated when imported. |
| DatabasePackageVersion | {empty} | A version number which will be set on all migrations in the __MigrationLog table on deployment. |
You can also set a number of comparison and script generation options to configure how your scripts are generated.
The following settings also exist in a new SQL Change Automation project file, which we recommend that you do not alter:
| Setting | Description |
|---|---|
IsSqlChangeAutomationProject | This is what identifies the SSDT project file as a SQL Change Automation project. |
| MinimumEngineVersion | This is the minimum version of SQL Change Automation required to develop on or deploy a particular project (newer settings may not be correctly interpreted by earlier versions of the product). |
| SqlChangeAutomationSchemaVersion | The version of the SQL Change Automation schema used to generate the migration log and schema snapshot tables. |
| DeployOnceSubFolder | The root folder where Migration Scripts are located. |
| DeployChangesSubFolder | The root folder where Programmable objects are located. |
| DeployChangesUnsupportedSubFolder | The folder where unsupported Programmable Objects are located. |
| IntermediateOutputPath | The location of dlls to be inlined when deploying assemblies. |
| PostDeploymentSubFolder | The folder where pre-deployment scripts are located. |
| PreDeploymentSubFolder | The folder where post-deployment scripts are located. |
| SchemaModelSubFolder | The root folder where offline schema model files are located. |
| MigrationOrdering | How migrations are grouped/ordered (this will have the value FilePath for newer projects). |
| SyncToOfflineSchemaModel | Enables generation of the offline schema model. |
| SkipBaselineCheck | Prevents builds from failing if the target database is currently populated and there is no baseline. It is preferable to provide a baseline script or set filters so that the target database is treated as empty rather than disabling this check. |
