Unit tests in project don't complete

[AartBluestoke]

many of them fail with " StdErr: *** An error occurred during deployment plan generation. Deployment cannot continue. Error SQL0: Required contributor with id 'AgileSqlClub.DeploymentFilterContributor' could not be loaded.

Contributor initialization error."

[AartBluestoke]

logs: Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2023-08-15T10:38:51 : SafeDirectoryCatalog: Extensibility failed to load DLL 'repos\DeploymentContributorFilterer\AgileSqlClub.SqlPackageFilter.IntegrationTests\bin\Debug\net4.8\SqlPackage.exe\AgileSqlClub.SqlPackageFilter.dll' due to an exception System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information. at System.Reflection.RuntimeModule.GetTypes(RuntimeModule module) at System.Reflection.RuntimeModule.GetTypes() at System.Reflection.Assembly.GetTypes() at System.ComponentModel.Composition.Hosting.AssemblyCatalog.get_InnerCatalog() at System.ComponentModel.Composition.Hosting.AssemblyCatalog.GetEnumerator() at System.Linq.Enumerable.Any[TSource](IEnumerable1 source) at Microsoft.SqlServer.Dac.Extensibility.DacExtensionHelper.TryLoadAssemblyCatalog(String file, ComposablePartCatalog& catalog) ThreadId=1 DateTime=2023-08-15T00:38:51.2073788Z Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2023-08-15T10:38:51 : SafeDirectoryCatalog: Extensibility failed to load DLL 'repos\DeploymentContributorFilterer\AgileSqlClub.SqlPackageFilter.IntegrationTests\bin\Debug\net4.8\SqlPackage.exe\Microsoft.Data.Tools.Schema.Tasks.Sql.dll' due to an exception System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information. at System.Reflection.RuntimeModule.GetTypes(RuntimeModule module) at System.Reflection.RuntimeModule.GetTypes() at System.Reflection.Assembly.GetTypes() at System.ComponentModel.Composition.Hosting.AssemblyCatalog.get_InnerCatalog() at System.ComponentModel.Composition.Hosting.AssemblyCatalog.GetEnumerator() at System.Linq.Enumerable.Any[TSource](IEnumerable1 source) at Microsoft.SqlServer.Dac.Extensibility.DacExtensionHelper.TryLoadAssemblyCatalog(String file, ComposablePartCatalog& catalog) ThreadId=1 DateTime=2023-08-15T00:38:51.6528620Z Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2023-08-15T10:38:51 : SafeDirectoryCatalog: Extensibility failed to load DLL 'repos\DeploymentContributorFilterer\AgileSqlClub.SqlPackageFilter.IntegrationTests\bin\Debug\net4.8\SqlPackage.exe\Microsoft.Data.Tools.Schema.Utilities.Sql.dll' due to an exception System.Reflection.ReflectionTypeLoadException: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information. at System.Reflection.RuntimeModule.GetTypes(RuntimeModule module) at System.Reflection.RuntimeModule.GetTypes() at System.Reflection.Assembly.GetTypes() at System.ComponentModel.Composition.Hosting.AssemblyCatalog.get_InnerCatalog() at System.ComponentModel.Composition.Hosting.AssemblyCatalog.GetEnumerator() at System.Linq.Enumerable.Any[TSource](IEnumerable1 source) at Microsoft.SqlServer.Dac.Extensibility.DacExtensionHelper.TryLoadAssemblyCatalog(String file, ComposablePartCatalog& catalog) ThreadId=1 DateTime=2023-08-15T00:38:51.6777494Z Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2023-08-15T10:38:51 : Microsoft.SqlServer.Dac.DacServicesException: An error occurred during deployment plan generation. Deployment cannot continue. Error SQL0: Required contributor with id 'AgileSqlClub.DeploymentFilterContributor' could not be loaded. ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Contributor initialization error. at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializeContributors() at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator() at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(Action1 msgHandler) at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager) --- End of inner exception stack trace --- ThreadId=1 DateTime=2023-08-15T00:38:51.9611984Z

[t-johnson]

Hi @AartBluestoke.

the tests in the "IntegrationTests" project are a bit of a pain to setup. the Readme for this project does not actually include all the steps required to get them to run.
in my local, i had to do the following:

1. Create and start sql local instance, using command line: sqllocaldb c Filter -s
2. Create the "Filters" database on that instance -i used SSMS, but powershell can be done too.
3. Update the sqlpackage tool that is in the AgileSqlClub.SqlPackageFilter.IntegrationTests\SqlPackage.exe\ folder to the version you want to test with.

I have these tests running on my local, currently using SqlPackage.exe version 15.

[AartBluestoke]

@t-johnson I initially suspected so, but i had created the localdb, with the Testing database - that wouldn't allow some of the tests to work.

note the error - it is a reflection error during dll load

it feels like there is a binding redirect between DLL's that are not binary compatable.

"'repos\DeploymentContributorFilterer\AgileSqlClub.SqlPackageFilter.IntegrationTests\bin\Debug\net4.8\SqlPackage.exe\Microsoft.Data.Tools.Schema.Utilities.Sql.dll' due to an exception System.Reflection.ReflectionTypeLoadException: "

2 of your tests pass anyway

(this is after i fixed the " /TargetServerName:localhost" on a couple of the tests over to localdb)

note: "can't talk to this db" gets a different error: " StdErr: *** Could not deploy package. Unable to connect to target server 'localhost'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The system cannot find the file specified"

[t-johnson]

@AartBluestoke what version of SqlPackage.exe are you using?

[AartBluestoke]

@AartBluestoke what version of SqlPackage.exe are you using?

the one that came with the project in the AgileSqlClub.SqlPackageFilter.IntegrationTests\SqlPackage.exe folder

[AartBluestoke]

found the issue:

1. the version you ship in the integration tests folder is older, somewhere approximately a pre-release 18.1 package from 2019 - and so fails the unit tests unless updated to the latest: (version from https://github.com/GoEddie/DeploymentContributorFilterer/blob/master/AgileSqlClub.SqlPackageFilter.IntegrationTests/SqlPackage.exe/sqlpackage.exe )

sqlPackage versions from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver16#1740-sqlpackage

2.visual studio ships with a non-standard sql-package that is approximately v19.2 - and not library compatible with the official release (no 16.0.9anything versions of sqlPackage are listed) :(

3. when you get the latest filter library and the latest sqlpackage it works. latest sqlPackage has a 162.x version number.

running dotnet tool install --local Microsoft.SqlPackage --version 162.0.52 as suggested at https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download or https://www.nuget.org/packages/Microsoft.SqlPackage/ helped.

[t-johnson]

Fixed in #36