SQL Change Automation 4

SQL Change Automation project settings

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:

SettingDefault valueDescription
DevelopmentSourceDatabaseWhere changes to the database schema are made. A development source represents the current state of the database in your development environment.
DSPMicrosoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProviderSQL Server version that scripts need to be compatible with. This will be derived from the target database if baselining is performed.
MigrationLogSchemaNamedboSchema 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).
BaseliningNoneThis 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.
ProvisionerDefaultThe provisioning strategy used by the project.
ProgrammableObjectHandlingUseRepeatableScriptsForAllObjectsHow 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.
SyncToOfflineSchemaModelBySchemaTrueWhether 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.
DefaultTransactionHandlingModeAutoWhether 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.scpfAbsolute 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.
GenerateSqlPackageFalseGenerates a package script during Visual Studio build.
BuildOptionSkipShadowDeployVSTrueWhen 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.
BuildOptionSkipThreePartNameCheckFalseThis 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.
EvaluateExecutionOrderOnImportTrueIndicates 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:

SettingDescription

IsSqlChangeAutomationProject

This is what identifies the SSDT project file as a SQL Change Automation project.
MinimumEngineVersionThis 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).
SqlChangeAutomationSchemaVersionThe version of the SQL Change Automation schema used to generate the migration log and schema snapshot tables.
DeployOnceSubFolderThe root folder where Migration Scripts are located.
DeployChangesSubFolderThe root folder where Programmable objects are located.
DeployChangesUnsupportedSubFolderThe folder where unsupported Programmable Objects are located.
IntermediateOutputPathThe location of dlls to be inlined when deploying assemblies.
PostDeploymentSubFolderThe folder where pre-deployment scripts are located.
PreDeploymentSubFolderThe folder where post-deployment scripts are located.
SchemaModelSubFolderThe root folder where offline schema model files are located.
MigrationOrderingHow migrations are grouped/ordered (this will have the value FilePath for newer projects).
SyncToOfflineSchemaModelEnables generation of the offline schema model.
SkipBaselineCheckPrevents 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.

Didn't find what you were looking for?