herdo / ssdtlifecycleextension

SQL Server Data Tools Lifecycle Extension for Visual Studio

SSDT Lifecycle Extension

SQL Server Data Tools Lifecycle Extension for Visual Studio

Repository Information

Build Status	Quality Gate	Code Coverage

Visual Studio Marketplace Information

Visual Studio Target	Current Version	Downloads	Installations	Rating
Visual Studio 2019
Visual Studio 2022

The SSDT Lifecycle Extension allows you to streamline and simplyify your workflow when working with SQL projects.

When should you use this extension?

If you need to manage a lot of SQL database objects, it's recommended to use a SQL database project to manage the source code of those objects.
If you want to add a versioned deployment process to this method of working with database objects, the SSDT Lifecycle extension will help you to do so. It'll take care of common pitfalls when working with SSDT, as well as simplify and standardize the process accros multiple teams and projects.

How do you use this extension?

Primarily, the extension adds a new context menu entry to SQL database projects:
From this menu you can start the script creation process, as well as open the configuration that defines how the extension will work for your project. For more details, please have a look at the documentation:

• Getting started
• Features
• Configuration

ssdtlifecycleextension's Issues

Wrong log output for copied files

Affected version: 1.3.1

Description: The new log output contains wrong path information, claiming to copy files to the bin directory instead of the artifacts directory.

Additional information:

Currently wrong output:

INFO: Copying files to target directory ...
TRACE: Copied file to C:\...\bin\Output\file1.dacpac ...
TRACE: Copied file to C:\...\bin\Output\file2.dacpac ...

Expected output:

INFO: Copying files to target directory ...
TRACE: Copied file to C:\...\_Deployment\latest\file1.dacpac ...
TRACE: Copied file to C:\...\_Deployment\latest\file2.dacpac ...

Open wiki documentation from configuration window

For each configuration option exists a detailed description in the GitHub wiki.
Help icons should be displayed in the configuration window that directly link tho those wiki articles.

Incomplete error message

Affected version: 1.3.0

Description: When the DacService fails, only the base exception message is returned. However, the actual Message property contains a lot more useful information. The whole message should be returned and logged.

Additional information:
Instead of just using e.GetBaseException().Message, add e.Messages to the returned errors array.

ReplaceUnnamedDefaultConstraintDropsModifier creates wrong script

The current ReplaceUnnamedDefaultConstraintDropsModifier currently creates this SQL code as replacement for unnamed default constraints:

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

The last row contains AND c.name = column_name, which is wrong.
Instead, it should be AND c.name = @column_name.

Extension doesn't load

Affected version: 1.0.3

Description: The extension fails to load when a SQL project is selected.

Additional information:
Stack trace from ActivityLog.xml:

SetSite failed for package [SSDTLifecycleExtensionPackage]Source: 'Unity.Container' Description: For more information add Diagnostic extension: Container.AddExtension(new Diagnostic())
Unity.ResolutionFailedException: For more information add Diagnostic extension: Container.AddExtension(new Diagnostic()) ---> System.InvalidOperationException: No public constructor is available for type SSDTLifecycleExtension.Shared.Contracts.Factories.IWorkUnitFactory. ---> Unity.Exceptions.InvalidRegistrationException: Eine Ausnahme vom Typ "Unity.Exceptions.InvalidRegistrationException" wurde ausgelöst.
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei Unity.Processors.ConstructorProcessor.<>c.<GetResolver>b__18_1(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_2(BuilderStrategy[] chain, BuilderContext& context)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name, InternalRegistration registration)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name)
   bei Unity.Builder.BuilderContext.Resolve(ParameterInfo parameter, Object value)
   bei Unity.Processors.ParametersProcessor`1.<>c__DisplayClass5_0.<CreateParameterResolvers>b__0(BuilderContext& context)
   bei Unity.Processors.ConstructorProcessor.<>c__DisplayClass19_0.<GetResolverDelegate>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_2(BuilderStrategy[] chain, BuilderContext& context)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name, InternalRegistration registration)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name)
   bei Unity.Builder.BuilderContext.Resolve(ParameterInfo parameter, Object value)
   bei Unity.Processors.ParametersProcessor`1.<>c__DisplayClass5_0.<CreateParameterResolvers>b__0(BuilderContext& context)
   bei Unity.Processors.ConstructorProcessor.<>c__DisplayClass19_0.<GetResolverDelegate>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_2(BuilderStrategy[] chain, BuilderContext& context)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name, InternalRegistration registration)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name)
   bei Unity.Builder.BuilderContext.Resolve(ParameterInfo parameter, Object value)
   bei Unity.Processors.ParametersProcessor`1.<>c__DisplayClass5_0.<CreateParameterResolvers>b__0(BuilderContext& context)
   bei Unity.Processors.ConstructorProcessor.<>c__DisplayClass19_0.<GetResolverDelegate>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_1(BuilderContext& context)
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei Unity.UnityContainer.<>c.<.ctor>b__58_1(BuilderContext& context)
   bei Unity.UnityContainer.Unity.IUnityContainer.Resolve(Type type, String name, ResolverOverride[] overrides)
   bei Unity.UnityContainerExtensions.Resolve[T](IUnityContainer container, ResolverOverride[] overrides)
   bei SSDTLifecycleExtension.DependencyResolver.Get[T]()
   bei SSDTLifecycleExtension.SSDTLifecycleExtensionPackage.<InitializeAsync>d__5.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.VisualStudio.Shell.AsyncPackage.<>c__DisplayClass19_0.<<Microsoft-VisualStudio-Shell-Interop-IAsyncLoadablePackageInitialize-Initialize>b__1>d.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.VisualStudio.Threading.JoinableTask.<JoinAsync>d__68.MoveNext()System.InvalidOperationException: No public constructor is available for type SSDTLifecycleExtension.Shared.Contracts.Factories.IWorkUnitFactory. ---> Unity.Exceptions.InvalidRegistrationException: Eine Ausnahme vom Typ "Unity.Exceptions.InvalidRegistrationException" wurde ausgelöst.
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei Unity.Processors.ConstructorProcessor.<>c.<GetResolver>b__18_1(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_2(BuilderStrategy[] chain, BuilderContext& context)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name, InternalRegistration registration)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name)
   bei Unity.Builder.BuilderContext.Resolve(ParameterInfo parameter, Object value)
   bei Unity.Processors.ParametersProcessor`1.<>c__DisplayClass5_0.<CreateParameterResolvers>b__0(BuilderContext& context)
   bei Unity.Processors.ConstructorProcessor.<>c__DisplayClass19_0.<GetResolverDelegate>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_2(BuilderStrategy[] chain, BuilderContext& context)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name, InternalRegistration registration)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name)
   bei Unity.Builder.BuilderContext.Resolve(ParameterInfo parameter, Object value)
   bei Unity.Processors.ParametersProcessor`1.<>c__DisplayClass5_0.<CreateParameterResolvers>b__0(BuilderContext& context)
   bei Unity.Processors.ConstructorProcessor.<>c__DisplayClass19_0.<GetResolverDelegate>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_2(BuilderStrategy[] chain, BuilderContext& context)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name, InternalRegistration registration)
   bei Unity.Builder.BuilderContext.Resolve(Type type, String name)
   bei Unity.Builder.BuilderContext.Resolve(ParameterInfo parameter, Object value)
   bei Unity.Processors.ParametersProcessor`1.<>c__DisplayClass5_0.<CreateParameterResolvers>b__0(BuilderContext& context)
   bei Unity.Processors.ConstructorProcessor.<>c__DisplayClass19_0.<GetResolverDelegate>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.Processors.MemberProcessor`2.<>c__DisplayClass8_0.<GetResolver>b__0(BuilderContext& c)
   bei Unity.UnityContainer.<>c__DisplayClass94_0.<OptimizingFactory>b__0(BuilderContext& c)
   bei Unity.Strategies.BuildPlanStrategy.PreBuildUp(BuilderContext& context)
   bei Unity.UnityContainer.<>c.<.ctor>b__58_1(BuilderContext& context)Unity.Exceptions.InvalidRegistrationException: Eine Ausnahme vom Typ "Unity.Exceptions.InvalidRegistrationException" wurde ausgelöst.

Close all windows when closing the solution

Affected version: 1.3.1

Description: When having a tool window of the extension open, and you open a solution without closing Visual Studio, the tool windows from the previous solution stay open and are operational.

Additional information:
Closing a solution or switiching to a different solution should try to close all open tool windows.

Log additional debug information when creating a script

When creating a script, the following information should be logged on the DEBUG level as well, before really creating the script:

• Current working directory
• All three full file paths used to generate the deployment script and report.

Misleading error message for missing publish profile

The error message for a missing publish profile currently says there is more than one.
This is obviously misleading ans should be changed.

SSDTLifecycleExtension/src/Shared/WorkUnits/VerifyPathsUnit.cs

Line 35 in e3dc7a5

$"The {nameof(ConfigurationModel.PublishProfilePath)} is set to \"{ConfigurationModel.UseSinglePublishProfileSpecialKeyword}\", but there's more than one publish profile in the directory. " +

Provide color scheme for logging

The current log messages are all in the same color.
Make use of the IClassifier components to provide colors for different log-levels: http://www.codewrecks.com/blog/index.php/2012/01/09/customize-output-windows-in-visual-studio/

Add configuration option to enable/disable "delete latest after versioned script generation"

When creating a versioned script, the files in the "latest" directory should be deleted, this is accomplished by #6 .
This option should be configurable.

Add a configuration option to enable or disable this feature (default: enabled).

Make RemoveSqlCmdStatementsModifier safer

The current RemoveSqlCmdStatementsModifier uses a single operation to determine all relevant SQLCMD statements. Split this statement into multiple remove operations.

Current:

model.CurrentScript = ForEachMatch(model.CurrentScript,
                                   "USE [$(DatabaseName)];",
                                   4,
                                   s => string.Empty);

Should be:

model.CurrentScript = ForEachMatch(model.CurrentScript,
                                   "USE [$(DatabaseName)];",
                                   0,
                                   s => string.Empty);
model.CurrentScript = ForEachMatch(model.CurrentScript,
                                   "IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'",
                                   0,
                                   s => string.Empty);
// etc...

Cannot create scripts

I installed this extension on VS 2019. Created a new SQL project. Scaffold the project as version 0.0.0.0. Add a table and saved it the project. Now when staring Script Creation again I still see the option of creating version 0.0.0.0. No changes are detected.

Invalid configuration combination for RemoveSqlCmdStatements

Affected version: 1.2.0

Description: RemoveSqlCmdStatements = true will create a wrong script, when certain publish profile settings are set to true.

Additional information:

• The setting CreateNewDatabase of the publish profile will add a USE [master] and more to the created script, after which the current database will be dropped, if it exists, and a new one is created. Afterwards, a USE [$(DatabaseName)] will be executed, which will switch the script back to the correct database.
• The setting BackupDatabaseBeforeChanges of the publish profile will add a IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) and more to the created script, after which the current database will be backed up. Afterwards, a USE [$(DatabaseName)] will be executed, which will switch the script back to the correct database.
• The setting ScriptDatabaseOptions of the publish profile will add multiple database statements that use the $(DatabaseName) variable.
• The setting ScriptDeployStateChecks of the publish profile will add multiple database statements that use the $(DatabaseName) variable.

The points above, in combination with the extensions configuration RemoveSqlCmdStatements will cause in a script that will execute not as intended, or won't run at all, depending on the executing application.

Import configuration into project

Allow the import of an existing configuration into the project.

Post-deployment is not included

Affected version: 1.1.0

Description: Post-deployment is not included in generated script.

Additional information:
Randomly affects script generation.

Improve name check

Affected version: 1.3.0

Description: The name of the DACPAC is now saved to a different location in the project file, namely DacApplicationName. This property should be evaluated as well.

Additional information:

• Evaluate DacApplicationName as primary source for the paths.
• Evaluate SqlTargetname as secondary source for the paths.
• Evaluate Name as the third source for the paths.

If the determined source for the paths doesn't match the SQL project name, write a warning message into the log output.

Add work unit to delete latest files after versioned script generation

Add a work unit to delete all files in the "latest" artifacts directory, when creating a versioned script in the artifacts directory.

Support Visual Studio 2022

The extension needs to be ported, so it can run as Visual Studio 2022 extension, as well as a Visual Studio 2019 extension.
See these upgrade guidelines: https://docs.microsoft.com/en-us/visualstudio/extensibility/migration/update-visual-studio-extension?view=vs-2022

Log copied files

Affected version: 1.3.0

Description: When files are copied from the bin directory to the artifacts directory, it's unclear which files are copied.

Additional information:
Add a list of the files that were copied (absolute paths) to the log output.

Improve error messages for missing XML nodes

The current error messages, when an important XML node is missing from the *.slqproj file aren't very helpful. Add additional information to each error message how the error can be resolved.

DependencyResolver.GetViewModel resolves wrong view model

When working with TFVC as source control provider, branches are manage on different paths.
Same projects, e.g. on a dev branch and release branch have the same name, but a different path.

When switching between the branches in the same Visual Studio instance, the previously opened project will still be used.

Steps to reproduce:

• Create sqlproj at C:\Temp\Dev\MyDatabase.sqlproj.
• Create sqlproj at C:\Temp\Release\MyDatabase.sqlproj as copy of Dev.
• Open script creation window for Release branch.
• Open Dev solution and open script creation window.

Error:
Even though the script creation window gets opened for the Dev branch, actions still happen on the Release branch.

Copy DACPAC to shared repository

One may use a lot of small SQL database projects that represent parts of the same database.
To create a compound database project that combines multiple small projects, the small projects should be available in a shared repository.
Provide an option to configure a repository path for DACPACs.

Add configuration option to enable/disable refactorlog cleanup

When creating a versioned script, the current refactorlog should be deleted, this is accomplished by #3 .
This option should be configurable.

• Add a configuration option to enable or disable this feature (default: disabled).

Add work unit to delete refactorlog after versioned script generation

Add a custom work unit to clean the refactor log after generating a versioned script.

Breaking change in referenced DACPAC fails the deployment script creation

Affected version: 1.3.1

Description: When trying to create the script for the attached LibrarySystem project, the script creation fails, saying that the column ZipCode cannot be found.

Additional information:
When consuming a shared DACPAC, e.g. referencing a column from the DACPAC in a view, this reference will be included correctly. However, when the referenced column is removed from the shared DACPAC, the product specific database project has to be updated as well. In this example, the referenced column has to be removed from the view.

However, after removing the column that doesn't exist any longer, then trying to create the deployment script, causes the following error:

ERROR: Error SQL0: The reference to external elements from the source named '[dbo].[Customer].[ZipCode]' could not be resolved, because no such source is loaded.
ERROR: Error SQL0: The reference to external elements from the source named '[dbo].[Customer].[ZipCode]' could not be resolved, because no such source is loaded.

Projects and structure to reproduce the issue can be found in the attached ZIP container.

However, using the attached PowerShell script, which calls sqlpackage.exe, works perfectly fine and creates the correct deployment script.

DataToReproduce.zip

DeploymentSkript_Generator.zip

Support different encodings

The generated deployment scripts may contain characters that are not supported by UTF8 encoding.

• Add an additional option to the project settings, allowing to set the encoding with which the generated script and report are saved.
• When writing the files, the set encoding should be used.

Improve error message when failing to find publish profile

When the publish profile cannot be determined, the following error message is currently shown:

ERROR: Failed to find publish profile.

The error message should be improved:

• State whether no profile was found, or the configuration is {SINGLE_PROFILE} and multiple profiles exist.
• Add a link to the documentation (https://github.com/Herdo/SSDTLifecycleExtension/wiki/Configuration#publish-profile-path).