Configuration

This page describes the possible configuration options in detail.

Paths

Artifacts path

The artifacts path defines the relative path to the .sqlproj file, where the DACPACs and artifacts generated by the extension will be stored. The artifacts directory itself contains sub-directories for each artifact version.

The default value for this configuration property is _Deployment.

Examples (using the project path C:\git\MySolution\MyProject\MyProject.Database.sqlproj):

• _Deployment --> C:\git\MySolution\MyProject\_Deployment
• ..\_Deployment --> C:\git\MySolution\_Deployment
• ..\Build\Database --> C:\git\MySolution\Build\Database

Publish profile path

The publish profile path defines the relative path to the .sqlproj file, where the publish profile used for script generation is located. The publish profile defines the way the deployment script is generated.

The default value for this configuration property is {SINGLE_PROFILE}.

When set to {SINGLE_PROFILE} will dynamically search for a single publish profile in the directory of the .sqlproj file. If none or more than one are found, an error will be raised, in which case you either have to create or specify a specific profile.

Examples (given the structure below):

C:\
  git\
    MySolution\
      ProductionProfile.publish.xml
      MyProject\
        MyProject.Database.sqlproj
        DevelopmentProfile.publish.xml
        IntegrationProfile.publish.xml

• {SINGLE_PROFILE} --> Will cause an error, because there is more than one profile in the directory of the .sqlproj file.
• DevelopmentProfile.publish.xml --> Will use the profile located at C:\git\MySolution\MyProject\DevelopmentProfile.publish.xml.
• ..\ProductionProfile.publish.xml --> Will use the profile located at C:\git\MySolution\ProductionProfile.publish.xml.

Shared DACPAC repository path

Legacy property See below for successor.

Shared DACPAC repository paths

The shared DACPAC repository path path defines the absolute path of the directory, where the current DACPAC will be copied to after the process of scaffolding or script generation completes.

The default value for this configuration property is null.

When set to to an absolute path, the DACPAC file generated by the SQL database project will be copied to that location. Any existing version of that DACPAC will be overwritten. Dependencies won't be copied.

Important: The path must be rooted (e.g. start with C:\) and end with a backslash \!

Script Creation Options

Build before script creation

Whether to build or not to build the SQL database project before creating the script.

When enabled, the project and the referenced database projects will be build before creating the script.
When disabled, the DACPAC files from the output directory will be used directly, even when those are outdated. If no DACPAC files are present, the script generation will fail.

The default value for this configuration property is true.

Create documentation

Whether to create or not to create the deployment report when creating the script.

When enabled, a deployment report will be generated alongside the script. It contains structured XML data for the operations that the script performs. This deployment report will be used by the version history view.
When disabled, no report will be generated.

The default value for this configuration property is true.

Comment out unnamed default constraint drops

Whether to comment out unnamed default constraint drops in the script, or leave them in the script as they are.

When enabled, the unnamed default constraint drops will be commented with --, stopping the script to fail due to invalid statements (see example below).
When disabled, the unnamed default constraint drops will be left as they are.

The default value for this configuration property is false.

Important: Only Comment out unnamed default constraint drops or Replace unnamed default constraint drops may be set to true, but not both!

Example:

PRINT 'Dropping unnamed default constraint on [dbo].[Author] ...'

GO
ALTER TABLE [dbo].[Author] DROP CONSTRAINT ; -- Runtime error

GO

When enabled, this will result in:

-- PRINT 'Dropping unnamed default constraint on [dbo].[Author] ...'

-- GO
-- ALTER TABLE [dbo].[Author] DROP CONSTRAINT ; -- No runtime error

-- GO

Replace unnamed default constraint drops

Whether to replace unnamed default constraint drops in the script, or leave them in the script as they are.

When enabled, the unnamed default constraint drops will be replaced with dynamic SQL that searches for the unnamed default constraints by schema, table and column, and dynamically drops them, no matter what name they have (see example below).
When disabled, the unnamed default constraint drops will be left as they are.

The default value for this configuration property is false.

Important: Only Comment out unnamed default constraint drops or Replace unnamed default constraint drops may be set to true, but not both!

Example:

PRINT 'Dropping unnamed default constraint on [dbo].[Author] ...'

GO
ALTER TABLE [dbo].[Author] DROP CONSTRAINT ; -- Runtime error

GO

When enabled, this will result in:

PRINT 'Dropping unnamed default constraint on [dbo].[Author] ...';

GO
DECLARE @schema_name sysname
DECLARE @table_name  sysname
DECLARE @column_name sysname
DECLARE @command     nvarchar(1000)

SET @schema_name = N'dbo'
SET @table_name = N'Author'
SET @column_name = N'LastName'

SELECT @command = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] DROP CONSTRAINT ' + d.name
 FROM sys.tables t
 JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
 JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = d.parent_column_id
WHERE t.name = @table_name
  AND t.schema_id = schema_id(@schema_name)
  AND c.name = column_name

EXECUTE (@command)

GO

Remove SQLCMD statements

Whether to remove common SQLCMD statements from the script, or leave them in the script as they are.

When enabled, the statements (from GO to GO) containing the following will be removed from the generated script:

• USE [$(DatabaseName)];
• IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
• :setvar __IsSqlCmdEnabled ""True""
• :on error exit
• :setvar DatabaseName

When disabled, the SQLCMD statements are left as they are.

The default value for this configuration property is false.

Cleanup

Delete refactorlog after versioned script generation

Whether to delete any refactorlog files within the project root, or leave them there as they are.

When enabled, the *.refactorlog files in the projects root directory will be deleted when creating a versioned script. When disabled, the files won't be deleted.

The default value for this configuration property is false.

Delete 'latest' files after versioned script

Whether to delete the contents of the latest artifacts directory, or leave them there as they are.

When enabled, all files within the latest artifacts directory will be deleted when creating a versioned script. When disabled, the files won't be deleted.

The default value for this configuration property is true.

Version Management

Version pattern

Defines the version pattern that is applied when creating versioned scripts.

You have to define any valid version pattern with two to four components. A component can either be a special keyword, or a Int32 number. The version patter will be applied to the version defined in the DACPAC.

The default value for this configuration property is {MAJOR}.{MINOR}.{BUILD}.

Examples (given the DACPAC version 4.35.1.20302):

• {MAJOR}.{MINOR} --> will create the version 4.35.
• {MAJOR}.0.{BUILD} --> will create the version 4.0.1.
• {MAJOR} --> Is not valid, because at least two components must be given.
• {MAJOR}.{MINOR}.0.0.0 --> Is not valid, because not more than four componnts may be given.
• {MAJOR}.{MINOR}.{BUILD}.{REVISION} --> will create the version 4.35.1.20302.
• {MAJOR}.{MINOR}.{BUILD}.0 --> will create the version 4.35.1.0.

Track DACPAC version

Whether to track the DACPAC version using the deployment script, or leave the version information out of the deployment script.

When enabled, the deployment script will contain three statements that will fill the table [dbo].[__DacpacVersion]. When disabled, the deplyoment script won't be modified to contain the DACPAC version.

The default value for this configuration property is false.

The table [dbo].[__DacpacVersion] is structured like this:

[dbo].[__DacpacVersion]
(
    DacpacVersionID	INT		NOT NULL	IDENTITY(1, 1),
    DacpacName		NVARCHAR(512)	NOT NULL,
    Major		INT		NOT NULL,
    Minor		INT		NOT NULL,
    Build		INT		NULL,
    Revision		INT		NULL,
    DeploymentStart	DATETIME2	NOT NULL,
    DeploymentEnd	DATETIME2	NULL,
    CONSTRAINT PK_DacpacVersion_DacpacVersionID PRIMARY KEY (DacpacVersionID)
)

Custom Texts

Custom header

A custom header text that will be inserted into the generated script.

When any text is specified, that text will be added at the very beginning of the generated script. When none is specified, no text will be inserted.

Use the special keyword {PREVIOUS_VERSION} as a placeholder to insert the previous DACPAC version into the generated script. use the special keyword {NEXT_VERSION} as a placeholder to insert the next DACPAC version into the generated script. Note, this either insert the formatted version, or latest, depending on your creation mode.

The default value for this configuration property is null.

Custom footer

A custom footer text that will be inserted into the generated script.

When any text is specified, that text will be added at the very end of the generated script. When none is specified, no text will be inserted.

Use the special keyword {PREVIOUS_VERSION} as a placeholder to insert the previous DACPAC version into the generated script. use the special keyword {NEXT_VERSION} as a placeholder to insert the next DACPAC version into the generated script. Note, this either insert the formatted version, or latest, depending on your creation mode.

The default value for this configuration property is null.