microsoft / dacextensions Goto Github PK

DACExtensions contains samples that extend Data-Tier Applications using DacFx. These samples include deployment contributors and static code analysis rules that can be used with Visual Studio as well as examples of how to use the DacFx public mode

License: MIT License

C# 99.38% Smalltalk 0.01% PowerShell 0.25% TSQL 0.37%

dacextensions's Introduction

DACExtensions

Important

This repository has been archived and the samples are no longer maintained. The development of DacFx and SQL projects is active, including the areas of extensibility and code analysis as seen in this repository. We welcome your feedback and issue reports over at https://github.com/microsoft/dacfx.

Project Description

DACExtensions contains API extensions and samples using the DacFx API to develop Data-Tier Applications.

These extensions and samples include an improved object model, deployment contributors and static code analysis rules that can be used with Visual Studio or directly with the DacFx public API.

Background Information

SQL Server Data Tools is built into Visual Studio 2013. For other versions of visual studio you can download SQL Server Data Tools from the Data Developer Center

SQL Server Data Tools provides an integrated environment for database developers to carry out all of their database design work for any SQL Server Platform.

DacFx is the core technology the SQL Server Data Tools leverages for incremental database deployments, modelling and validation of database schemas and other key functionality. DacFx provides a DacServices API that supports programmatic deployment of Dacpac files. DacFx has a number of public extensibility points that customers can implement. Some possible functionality includes:

Programmatic examinations of database schemas (as represented in a Visual Studio database project, a Dacpac and even a target database when used during deployment)
Modification of the deployment plans used to incrementally deploy schema updates from a Dacpac to a database
Static Code Analysis rules provide build-time support for analyzing the database schema and warning users of potential issues.

These samples are primarily intended to show users how to create their own extensions, test them and use them in deployment. They include solutions to real customer problems and can be used as-is if you have the same issue. Ideally you will take this project, extend it and create your own solutions.

Prerequisites

An installation of SQL Server Data Tools or DacFx is required to run these samples. The sample projects include references to DLLs in a Visual Studio installation with the most recent release of SQL Server Data Tools.

For Visual Studio 2013 the install directory will is "%ProgramFiles(x86)%\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120".
This is set as the current hint path in all sample projects
For Visual Studio 2012 the install directory is "%ProgramFiles(x86)%\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120".
The hint path in all sample projects must be updated if you want to target Visual Studio 2012 DLLs.
For DacFx the install directory is "%ProgramFiles(x86)%\Microsoft Server Server \Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120".
The hint path in all sample projects must be updated if you want to target the DacFx DLLs.

This project uses Git for source code control. Visual Studio 2013 has Git support built in, and for Visual Studio 2012 please download the Visual Studio Tools for Git plugin.

Usage

The samples in this project are provided as-is. To run the samples, we recommend you download the source code and attach a debugger while running the sample console application and unit test code.

The full tutorial explaining how to use the public APIs can be found here.

Samples Installation

These samples assume the latest SQL Server Data Tools updates for Visual Studio 2013 are installed. To develop using Visual Studio 2012 please modify the .csproj files to change "C:\Program Files (x86)\Microsoft Visual Studio 12.0" to "C:\Program Files (x86)\Microsoft Visual Studio 11.0", and ensure the latest version of SQL Server Data Tools is installed for Visual Studio 2012.

These samples are intended to work in tandem with the walkthroughs at http://msdn.microsoft.com/en-us/library/dn268597(v=vs.103).aspx and with the standard SQL Server Data Tools help documentation. They show how to bootstrap an extensions library for Static Code Analysis rules and contributors and test them without installing anything into Visual Studio.

If you wish to install any samples DLL into visual studio you need to sign the DLLs per the walkthrough instructions:

To sign and build the assembly

On the Project menu, click Properties .
Click the Signing tab.
Click Sign the assembly.
In Choose a strong name key file , click .
In the Create Strong Name Key dialog box, in Key file name , type MyRefKey.
(optional) You can specify a password for your strong name key file.
Click OK.
On the File menu, click Save All.
On the Build menu, click Build Solution.

Then copy them to the extension installation directory:

VS 2013: %ProgramFiles(x86)%\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Extensions
VS 2012: %ProgramFiles(x86)%\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Extensions

Code of Conduct

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

dacextensions's People

Contributors

Stargazers

Watchers

dacextensions's Issues

IgnoreColumnOrder not working with temporal tables

Hi Guys,

We are experiencing major issues managing temporal tables through SSDT. One major issue is that simple column changes are causing table rebuilds.

Problem

Adding columns to temporal tables are triggering table rebuilds which is a big problem for realtime systems or tables with lots of data. IgnoreColumnOrder=True does not work for temporal tables. I tried configuring the desired behaviour by using a publish profile and specifying the property directly ( /p:IgnoreColumnOrder=true ) resulting in the same output.

Change Script Result:

PRINT N'Starting rebuilding table [dbo].[tb_Settings]...';


BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_tb_Settings] (
    [SettingId]      INT                                         NOT NULL,
    [Key]            NVARCHAR (255)                              NOT NULL,
    [DisplayName]    NVARCHAR (255)                              NOT NULL,
    [Description]    NVARCHAR (MAX)                              NULL,
    [DateTimeValue2] DATETIME2 (2)                               NULL,
    [Value]          NVARCHAR (255)                              NULL,
    [SysStart]       DATETIME2 (2) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEnd]         DATETIME2 (2) GENERATED ALWAYS AS ROW END   NOT NULL,
    [DateValue]      DATETIME2 (2)                               NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_SettingId1] PRIMARY KEY CLUSTERED ([SettingId] ASC),
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[tb_Settings_HISTORY], DATA_CONSISTENCY_CHECK=ON));

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[tb_Settings])
    BEGIN
        IF EXISTS (SELECT TOP 1 1
                   FROM   [sys].[tables]
                   WHERE  [name] = N'tmp_ms_xx_tb_Settings'
                          AND SCHEMA_NAME(schema_id) = N'dbo'
                          AND temporal_type = 2)
            BEGIN
                ALTER TABLE [dbo].[tmp_ms_xx_tb_Settings] SET (SYSTEM_VERSIONING = OFF);
            END
        IF EXISTS (SELECT TOP 1 1
                   FROM   [sys].[periods]
                   WHERE  [period_type] = 1
                          AND OBJECT_ID('[dbo].[tmp_ms_xx_tb_Settings]', 'U') = object_id)
            BEGIN
                ALTER TABLE [dbo].[tmp_ms_xx_tb_Settings] DROP PERIOD FOR SYSTEM_TIME;
            END
        INSERT INTO [dbo].[tmp_ms_xx_tb_Settings] ([SettingId], [Key], [DisplayName], [Description], [Value], [DateTimeValue2], [SysStart], [SysEnd], [DateValue])
        SELECT   [SettingId],
                 [Key],
                 [DisplayName],
                 [Description],
                 [Value],
                 [DateTimeValue2],
                 [SysStart],
                 [SysEnd],
                 [DateValue]
        FROM     [dbo].[tb_Settings]
        ORDER BY [SettingId] ASC;
        ALTER TABLE [dbo].[tmp_ms_xx_tb_Settings]
            ADD PERIOD FOR SYSTEM_TIME (SysStart, SysEnd);
        ALTER TABLE [dbo].[tmp_ms_xx_tb_Settings] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[tb_Settings_HISTORY], DATA_CONSISTENCY_CHECK=ON));
    END

IF EXISTS (SELECT TOP 1 1
           FROM   [sys].[tables]
           WHERE  [name] = N'tb_Settings'
                  AND SCHEMA_NAME(schema_id) = N'dbo'
                  AND temporal_type = 2)
    BEGIN
        ALTER TABLE [dbo].[tb_Settings] SET (SYSTEM_VERSIONING = OFF);
    END

DROP TABLE [dbo].[tb_Settings];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_tb_Settings]', N'tb_Settings';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_SettingId1]', N'PK_SettingId', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Testing

Tested using SQLPackage 18.5 and 18.5.1.

Please advise if this can be resolved in the next version of SQLPackage / DacFx.

Import bacpac: Object reference not set to an instance of an object.

When i try to import a bacpac file i get the error:
Object reference not set to an instance of an object.

This only started happening after upgrading sqlpackage.exe to version v4.1.
When using version 4.0 everything works fine.

Stacktrace:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2020-04-16T21:23:40 : SqlPackage failed unexpectedly Exception: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateFragment(Int32 operation, IModelElement element) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.cs:line 408 at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateSteps(Int32 operation, IModelElement element) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.cs:line 297 at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildDependencyOrderedSteps(Int32 operation, List1 classOrder, List1 operationOrder, Dictionary2 changes, Boolean preserveGraphs, Dictionary2& relating, Dictionary2& related) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\Analyzers\PlanMediator.cs:line 1365
at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildOperations() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\Analyzers\PlanMediator.cs:line 803
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnGeneratePlan() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.cs:line 340
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.GeneratePlan(List1 drops) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentPlanGenerator.cs:line 890 at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.BuildPlan() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.cs:line 1746 at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.CreatePlan() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.Controller.cs:line 126 at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass16_1.<CreatePlanInitializationOperation>b__1() in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 151 at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Dac\Logging\OperationLogger.cs:line 48 at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass16_0.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 104 at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\Operation.cs:line 72 at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\ReportMessageOperation.cs:line 44 at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 63 at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 63 at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 470 at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 26 at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action3 reportPlanOperation, Boolean executePlan) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 826
at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacImportOptions importOptions, Nullable1 cancellationToken) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 2677 at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoImportBacpacOperation(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 850 at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 212 at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 156 at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 50 *** An unexpected failure occurred: Object reference not set to an instance of an object..

Deploying Dacpac's via Powershell using Service Principals

Hi,

I'm trying to deploy a dacpac to Azure SQL, I would like to deploy it using a service principal and PowerShell. But I cannot figure out how the connection string should work.

Using a SQL Login I would do something like this:

$dacServices = New-Object Microsoft.SqlServer.Dac.DacServices $ConnectionString

Where the connection string container the login and password.

Looking at the constructors for DacServices here there is a IUniversalAuthProvider which I think should be able to do what I want. But I cannot figure out how to construct it in PowerShell. I was expecting something like:

$Auth = New-Object Microsoft.SqlServer.Dac.IUniversalAuthProvider $AccessToken
$dacServices = New-Object Microsoft.SqlServer.Dac.DacServices $ConnectionString $Auth

Where Access Token is obtained from AAD via a Token request.

Another example I can find is in the Azure DevOps tasks for the Dacpac deploy here.

In Utility.ps1 there is a function called Get-AADAuthenticationConnectionString - but this seems to only work with AAD credentials (and not 2FA).

Is this possible?

Where to report bugs from Microsoft.SqlServer.TransactSql.ScriptDom.dll?

Hi,

It is my understanding that the DAC project uses Microsoft.SqlServer.TransactSql.ScriptDom.dll

Is there any change that this DLL will be open sourced so that we can report bugs and contribute?

There doesn't seem to be an official repo / place for it?

For now, I reported a bug in the sqltoolsservice project (see microsoft/sqltoolsservice#868) but the DACExtensions project is also somewhat related so I'll ask the question here as well.

Gets killed by OOM when running on Linux.

When extracting a bacpac, it consumes a large amount of memory on the Linux and then gets killed by OOM.

When OOM score is adjusted manually, the VM dies.

It does manage to produce a .bacpac file with model.xml, DacMetadata.xml and [Content_Types].xml. Model.xml is 10.9MB.

Any way to get the SQL Database project output folder and dacpac file name programatically?

I cannot get the name of the dacpac file to built.

How can I get the current Output path (for example: "bin/Debug") and file Name of the .dacpac??

It seems to be buried deep inside internal classes in Microsoft.VisualStudio.Data.Tools.Package.dll

SchemaComparisonResult.Exclude slow for encrypted objects

When trying to exclude a bunch of encrypted stored procedures from a SchemaComparisonResult, the process stays hung for as long as 5s.

In my case, I have to exclude about 80 stored procedures from 15 databases, so the process of excluding objects can take several hours. This only happens when excluding encrypted stored procedures, and I'm searching for the IsEncrypted diff name.

What is happening behind the scenes when I call the Exclude method that is taking so much time?

Thank you

Running .NET Core SqlPackage from a Linux Host against a Windows-based SQL Server results in errors

I'm generating a dacpac file using sqlpackage running on Linux from a SQL Server database hosted on a SQL Server running on Windows.

Here's the syntax I'm using:

sqlpackage /Action:Extract /TargetFile:schema.dacpac /SourceServerName:servername.domain.com /SourceDatabaseName:test /SourceUser:deploytest /SourcePassword:VerySecurePassword /Diagnostics:True /p:Storage=Memory /p:ExtractReferencedServerScopedElements=False /p:ExtractUsageProperties=False /p:IgnoreUserLoginMappings=True

Both the SQL Server and database collations are SQL_Latin1_General_CP1_CI_AS (I've also tested with Latin1_General_CP1_CI_AS).

sqlpackage tool fails to generate the dacpac. Here's the highlight from the diagnostics log (full log is below):

System.TypeInitializationException: The type initializer for 'Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation' threw an exception. ---> System.ArgumentException: Collation (1033) is not supported on this computer.

It appears like there's a collation conflict... How can we fix it?

Connecting to database 'test' on server 'servername.domain.com'.
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2020-02-19T20:55:00 : Initialized ModelSchema - elements 149
Microsoft.Data.Tools.Diagnostics.Tracer Information: 19 : 2020-02-19T20:55:00 : IsolatedStorageFile probe success
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : Logging Ambient Settings...
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : DatabaseLockTimeout: 60000
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : QueryTimeout: 60
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : LongRunningQueryTimeout: 0
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysRetryOnTransientFailure: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : ConnectionRetryHandler:
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : MaxDataReaderDegreeOfParallelism: 8
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : TraceRowCountFailure: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : TableProgressUpdateInterval: 300
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : UseOfflineDataReader: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : StreamBackingStoreForOfflineDataReading: File
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : ModelFilePath:
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : MasterReferenceFilePath:
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : DisableIndexesForDataPhase: True
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : ReliableDdlEnabled: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : ImportModelDatabase: True
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : SupportAlwaysEncrypted: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysEncryptedWizardMigration: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysEncryptedWizardPreserveTable: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysEncryptedWizardUseOnlineApproach: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysEncryptedWizardDowntimeInSeconds: 300
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysEncryptedWizardMaxDivergingIterations: 5
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysEncryptedWizardMaxIterations: 100
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : AlwaysEncryptedWizardMaxIterationDurationInDays: 3
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : SkipObjectTypeBlocking: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : DoNotSerializeQueryStoreSettings: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : LargeObjectHeapCompaction: False
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : LargeObjectHeapCompactionIntervalInSeconds: 30
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : ReferencePaths: System.String[]
Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2020-02-19T20:55:00 : DeploymentContributorPaths: System.String[]
Extracting schema
Extracting schema from database
Microsoft.Data.Tools.Diagnostics.Tracer Verbose: 1 : 2020-02-19T20:55:00 : Entering ExtensionTypeLoader.LoadTypes().
Time elapsed 00:00:03.75
Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2020-02-19T20:55:04 : SqlPackage failed unexpectedly
 Exception: Microsoft.Data.Tools.Schema.Extensibility.ExtensibilityException: The extension type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModelConstructor could not be instantiated. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation' threw an exception. ---> System.ArgumentException: Collation (1033) is not supported on this computer.
   at Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation..ctor(Int32 lcid, Boolean caseSensitive) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\ModelCollation.cs:line 146
   at Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation..cctor() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\ModelCollation.cs:line 96
   --- End of inner exception stack trace ---
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModelConstructor..ctor() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlSchemaModelConstructor.cs:line 90
   --- End of inner exception stack trace ---
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean wrapExceptions, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean wrapExceptions, Boolean skipCheckThis, Boolean fillCache)
   at Microsoft.Data.Tools.Schema.Extensibility.ExtensionTypeLoader.InstantiateType[T](Type type) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Extensibility\ExtensionTypeLoader.cs:line 666
   --- End of inner exception stack trace ---
   at Microsoft.Data.Tools.Schema.Extensibility.ExtensionTypeLoader.InstantiateType[T](Type type) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Extensibility\ExtensionTypeLoader.cs:line 679
   at Microsoft.Data.Tools.Schema.DatabaseSchemaProvider.GetServiceConstructor[TServiceConstructor]() in F:\B\16846\6200\Sources\Product\Source\SchemaSql\DatabaseSchemaProvider.cs:line 139
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.CreateDataSchemaModel(SqlReverseEngineerConnectionContext context, ErrorManager errors, Boolean optimizeForQuery, Boolean isMsdb, ModelStorageType modelType, Boolean storeSourceCodePositionAnnotations) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlReverseEngineerImpl.cs:line 3009
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.CreateModel(SqlReverseEngineerConnectionContext context, ErrorManager errorManager, Boolean optimizeForQuery, Boolean isMsdb, ModelStorageType modelType, Boolean storeSourceCodePositionAnnotations) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlReverseEngineerImpl.cs:line 2303
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineer.PopulateAll(SqlReverseEngineerConnectionContext context, ReverseEngineerOption option, ErrorManager errorManager, Boolean filterManagementScopedElements, SqlImportScope importScope, Boolean optimizeForQuery, ModelStorageType modelType) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\SqlReverseEngineer.cs:line 218
   at Microsoft.Data.Tools.Schema.SchemaModel.ReverseEngineerExtensionMethods.ImportDatabase(SqlReverseEngineer reverseEngineer, SqlReverseEngineerConnectionContext context, ReverseEngineerOption option, ErrorManager errorManager, Boolean filterManagementScopedElements, Boolean populateReferencedServerScopedElements, Boolean trimDanglingReferencesOnServerScopedElements, Boolean removeUnReferencedServerObjects, ModelStorageType modelStorageType, Boolean removeUnreferencedDbCredential) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\SchemaModel\ReverseEngineerExtensionMethods.cs:line 196
   at Microsoft.SqlServer.Dac.ExtractOperation.<>c__DisplayClass14_0.<CreateReverseEngineerOperation>b__0(Object operation, CancellationToken token) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\ExtractOperation.cs:line 179
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\Operation.cs:line 72
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\ReportMessageOperation.cs:line 44
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 63
   at Microsoft.SqlServer.Dac.ExtractOperation.<>c__DisplayClass21_0.<Microsoft.SqlServer.Dac.IOperation.Run>b__0() in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\ExtractOperation.cs:line 390
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Dac\Logging\OperationLogger.cs:line 49
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 26
   at Microsoft.SqlServer.Dac.DacServices.Extract(Func`1 streamGetter, String databaseName, String applicationName, Version applicationVersion, String applicationDescription, IEnumerable`1 tables, DacExtractOptions extractOptions, Nullable`1 cancellationToken, DacLoggingContext extractLoggingContext) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 300
   at Microsoft.SqlServer.Dac.DacServices.Extract(String packageFileName, String databaseName, String applicationName, Version applicationVersion, String applicationDescription, IEnumerable`1 tables, DacExtractOptions extractOptions, Nullable`1 cancellationToken) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 234
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoExtractDacpacOperation(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 661
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandLineArguments parsedArgs) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 206
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 156
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args) in F:\B\16846\6200\Sources\Product\Source\SqlPackage\Program.cs:line 50
*** An unexpected failure occurred: The extension type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModelConstructor could not be instantiated..

Microsoft.SqlServer.Dac.DacServicesException for PostDeployment only project

I'm using the nuget package Microsoft.SqlServer.DACFx 150.4441.1-preview

I have a database project used for sample data that only contains post deployment scripts.

The publish profile works fine in Visual Studio (2019), however, the same publish profile fails with the exception:
Microsoft.SqlServer.Dac.DacServicesException : Cannot deploy to existing database when upgrading has been disabled.

The publish profile is pretty basic:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>MyTargetDb</TargetDatabaseName>
    <DeployScriptFileName>MyTargetDb_SampleData.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=(localdb)\mssqllocaldb;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropConstraintsNotInSource>False</DropConstraintsNotInSource>
    <DropDmlTriggersNotInSource>False</DropDmlTriggersNotInSource>
    <DropExtendedPropertiesNotInSource>False</DropExtendedPropertiesNotInSource>
    <DropIndexesNotInSource>False</DropIndexesNotInSource>
    <DropStatisticsNotInSource>False</DropStatisticsNotInSource>
    <IncludeTransactionalScripts>False</IncludeTransactionalScripts>
    <IgnoreAnsiNulls>False</IgnoreAnsiNulls>
    <VerifyCollationCompatibility>False</VerifyCollationCompatibility>
    <VerifyDeployment>False</VerifyDeployment>
  </PropertyGroup>
</Project>

The publishing code is:

var dac = new DacServices(publishConnString);

using (var dbPackage = DacPackage.Load(SampleData, DacSchemaModelStorageType.Memory))
{
    var publishProfile = DacProfile.Load(PublishProfileSampleData);

    var options = publishProfile.DeployOptions;
    //Tried setting the below options - no change in error
    //options.BlockOnPossibleDataLoss = false;
    //options.BlockWhenDriftDetected = false;
    //options.AllowIncompatiblePlatform = true;

    //PostDeployment script is present - this looks like the correct sql
    string sql = new StreamReader(dbPackage.PostDeploymentScript).ReadToEnd();
   
    //fails with  Cannot deploy to existing database when upgrading has been disabled.`
    dac.Deploy(dbPackage, dbName, options: options);
}

Possible bug: SqlPackage 150 /a:script ignoring function dependencies

I've been struggling with this issue for most of the morning and I'm ready to suggest this is a bug in SqlPackage.exe 150

I extract a dacpac using the following command:

C:\"Program Files"\"Microsoft SQL Server"\150\DAC\bin\SqlPackage.exe /a:Extract /ssn:$server /sdn:$dbName /st:300 /p:CommandTimeout=300 /tf:obj\$dbName\extracted.dacpac

And publish a script using:

C:\"Program Files"\"Microsoft SQL Server"\150\DAC\bin\SqlPackage.exe /a:Script /tcs:"Server=$server`;Database=$newDbName`;Trusted_Connection=True`;Connection Timeout=300`;" /p:CommandTimeout=300 /p:ExcludeObjectType=Logins /p:ExcludeObjectType=Users /p:ExcludeObjectType=RoleMembership /p:IgnoreNotForReplication=true /p:UnmodifiableObjectWarnings=false /sf:obj\$dbName\extracted.dacpac /op:obj\$dbName\publish_script.sql

`The script generates but it fails when I try to execute it with the error:

Procedure MY_FUNCTION, Line 39 Invalid object name 'MY_OTHER_FUNCTION'
If I examine the script I can see the following:

LINE 300: PRINT N'Creating [dbo].[MY_FUNCTION]...'
... More code ...
LINE 400: PRINT N'Creating [dbo].[MY_OTHER_FUNCTION]...';

I've gone as far as digging into the extracted dacpac to confirm that the model.xml is picking up the dependency MY_FUNCTION has on MY_OTHER_FUNCTION. In fact the model.xml has them in the correct order.

I have also verified that this isn't a case of a circular dependency. MY_OTHER_FUNCTION is dependent on one table that was created back on LINE 100.

Why is the generated script trying to create them out of order?

If it helps MY_OTHER_FUNCTION is a SqlMultiStatementTableValuedFunction and MY_FUNCTION is a SqlInlineTableValuedFunction

Running DacServices.Extract() from a .NET Core 3.1 application results in errors

Hello,

I'm trying to generate a DacPac directly from .NET Core application code.
My application is configured as a .NET Core 3.1 console application.

Here's an example code I'm running from the console app running on my Linux (CentOS 7.5 base image) host:

using var memoryStream = new MemoryStream();
var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"

var dacServices = new DacServices(connectionString);

var dacPacExtractOptions = new DacExtractOptions
                                       {
                                               DatabaseLockTimeout = options.DatabaseLockTimeout,
                                               CommandTimeout = options.DatabaseCommandTimeout,
                                               ExtractAllTableData = true,
                                               ExtractApplicationScopedObjectsOnly = false,
                                               ExtractReferencedServerScopedElements = true,
                                               ExtractUsageProperties = false,
                                               IgnoreExtendedProperties = false,
                                               IgnorePermissions = true,
                                               IgnoreUserLoginMappings = true,
                                               LongRunningCommandTimeout = options.DatabaseCommandTimeout,
                                               Storage = DacSchemaModelStorageType.Memory,
                                               VerifyExtraction = true
                                       };

dacServices.Extract(memoryStream, "myDataBase", "myApplication", new Version(1,0,0), extractOptions: dacPacExtractOptions);

I've tried publishing a single file and non-single file, running it with or without sudo. The error I get in all cases is:

Microsoft.Data.Tools.Schema.Extensibility.ExtensibilityException: The extension type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModelConstructor could not be instantiated.
 ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
 ---> System.TypeInitializationException: The type initializer for 'Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation' threw an exception.
 ---> System.ArgumentException: Collation (1033) is not supported on this computer.
   at Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation..ctor(Int32 lcid, Boolean caseSensitive)
   at Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.Data.Tools.Schema.SchemaModel.ModelCollation.get_DefaultCollationCaseInsensitive()
   at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModelConstructor..ctor()
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModelConstructor..ctor()
   --- End of inner exception stack trace ---
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean wrapExceptions, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& hasNoDefaultCtor)
   at System.RuntimeType.CreateInstanceDefaultCtorSlow(Boolean publicOnly, Boolean wrapExceptions, Boolean fillCache)
   at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, Boolean wrapExceptions)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic, Boolean wrapExceptions)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at Microsoft.Data.Tools.Schema.Extensibility.ExtensionTypeLoader.InstantiateType[T](Type type)
   --- End of inner exception stack trace --

Surprisingly, you're going to get the same error if you run sqlpackage to generate the dacpac directly, without sudo (I've described the issue/resolution #37 ). However, with calling Dac tools directly from .NET Core results in this error independent of elevation level.

Supplying a DeploymentPlanModifier Directly

Currently in DacFx, one is able to add a DeploymentPlanModifier to the deployment by specifying the string ID in DeploymentOptions:

var options = new DeploymentOptions 
{ 
    AdditionalDeploymentContributors = "Some.Id.Here"
};

However, that requires that there be a separate DLL either alongside or in a hardcoded extensions folder, which I currently cannot do. Is there any way of directly supplying the API with either the type of the plan modifier or even an instance to skip the whole assembly search process?

I've been slogging through ILSpy for a while and this looks buried way in there, so I'm not immediately seeing a way of injecting anything into the deploy process. There's an awful lot of internal only API.

As a side note, is there any plan to open source DacFx? There's the opportunity to build some really cool tooling on top but it's pretty hard to get at some of the code surface.

Contributor DLL in DACPAC package folder does not load automatically

I see from #25 that DacFX should support automatically loading contributor DLLs from the same directory as the DACPAC. However, I have not been able to get that to work.

I can successfully get the third method introduced in that issue - specifying the path as a parameter to sqlpackage.exe - to work.

Example command line that works:

sqlpackage /Action:Publish /Profile:Database.publish.xml /TargetDatabaseName:Northwind /TargetServerName:MyDatabaseServer /p:AdditionalDeploymentContributors=CompanyName.DeploymentFilterContributor /SourceFile:Database.dacpac /p:AdditionalDeploymentContributorPaths=D:\SsdtTesting

My DACPAC file is in D:\SsdtTesting, with the contributor DLL alongside it. The above works as expected. But leaving off that last parameter causes it to fail:

*** An error occurred during deployment plan generation. Deployment cannot continue.
Error SQL0: Required contributor with id 'InternalApps.DeploymentFilterContributor' could not be loaded.

This is with a Windows (non-.NET Core) install of DacFX 18.3.1 (since that's the version currently installed on Microsoft's hosted pipeline agents, my eventual target).

Please let me know if there's any more information I can provide.

Error when building SSDT Rules Test cases on linux

We set up the default build action for our SSDT rules, and received this error when running the unit tests:

Starting test execution, please wait...
A total of 1 test files matched the specified pattern.
  Failed TestAvoidNotForReplication [115 ms]
  Error Message:
   Assert.Fail failed. Exception thrown for ruleId 'Rules.SRD0065' for test cases 'AvoidNotForReplication': Could not load type of field 'Microsoft.Data.Tools.Schema.Common.Diagnostics.EtwProvider:m_provider' (1) due to: Could not resolve type with token 01000013 from typeref (expected class 'System.Diagnostics.Eventing.EventProvider' in assembly 'System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089') assembly:System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 type:System.Diagnostics.Eventing.EventProvider member:(null)
  Stack Trace:
	at SqlServer.Rules.Tests.TestCasesBase.GetTestCaseProblems (System.String testCases, System.String ruleId) [0x0007d] in <8795a995719e46f88415a2776965660d>:0 
  at SqlServer.Rules.Tests.Performance.DesignTestCases.TestAvoidNotForReplication () [0x00001] in <8795a995719e46f88415a2776965660d>:0 
  at (wrapper managed-to-native) System.Reflection.RuntimeMethodInfo.InternalInvoke(System.Reflection.RuntimeMethodInfo,object,object[],System.Exception&)
  at System.Reflection.RuntimeMethodInfo.Invoke (System.Object obj, System.Reflection.BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x0006a] in <533173d24dae460899d2b10975534bb0>:0

Here is the build:
https://github.com/tcartwright/SqlServer.Rules/runs/3257647960?check_suite_focus=true

When we switched the operating system to windows for the build it worked. Is it possible to build and run these unit tests on linux? Is there something we can change in the github action?

We built our rules based upon the examples here. Thank you btw for that, much appreciated.

This repo is missing important files

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

SqlPackage `CREATE function` casing issues

Updated SqlPackage 140 x86 to 150 x64 with a local install of SqlServer 2019 Developer.

Publishing SSDT project via SqlPackage 150 alters all my functions and nothing else, in consecutive publishes, when code does not change. Before I used to publish via SqlPackage 140 with no issues at all. After update to SqlPackage 150, functions alter on every publish, regardless of changes.

I always use(d) lower-case create function but Schema Compare tells me CREATE function is expected. While no other difference is pointed out. Also, I use create table/view/procedure/trigger... and all these have no issues (if I don't change any code, publishing publishes nothing), but now only functions are altered.

SqlPackage 140 was always quirky but I learned to work around:

don't use iif, use case to avoid any re-creates
keep entire case when then ... end on a single line no matter how long it is
always wrap in parenthesis (default values) and (computed fields)
name all constraints (pks, uniques, checks) to avoid drop/re-create
...there are some more I'm forgetting right now...

What do I do about this one?

SqlPackage Publish works, Import does not

I have an existing Linux-based dockerfile, which publishes some dacpacs to a local database and this works fine.

However, I have now changed it because I want to import a bacpac including data. I have changed the sqlpackage command only to specify /a:Import and it points to a bacpac instead of a dacpac but it doesn't work, I see this error logged:

Importing to database 'SPC_Test' on server 'localhost'.
Creating deployment plan
Initializing deployment
*** Error importing database:Could not import 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.
Cannot open database "SPC_Test" requested by the login. The login failed.
Login failed for user 'sa'.

My understanding from msdn is that if the database does not exist, it will be created but the error seems to suggest this is not the case.

If this is the case, the error message should be more specific "Cannot import into new database, create the database first". If this is not the case, then I am stuck as to what I have done wrong. The relevant excerp from the dockerfile is below:

ENV ACCEPT_EULA="Y"
ENV SA_PASSWORD="P@ssW0rd!"

# Install Unzip
# Install SQLPackage for Linux and make it executable
# Get bacpac/dacpac from build
COPY Database/bin/Release/*.bacpac /tmp/

# See https://stackoverflow.com/a/51589787/488695 about waiting for sql to start
RUN ( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started" \
    && /opt/sqlpackage/sqlpackage /a:Import /tsn:localhost /tdn:SPC_Test /tu:sa /tp:$SA_PASSWORD /sf:/tmp/FuncTest.bacpac \
	&& rm /tmp/*.bacpac && pkill sqlservr

sqlpackage publish fails to create views in correct order (view on view)

My database has 'view1', then another 'view2' that selects from view1. sqlpackage publish fails with an error like:

Error SQL72914: Core Microsoft SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure View2, Line 24 Invalid object name 'MyDatabase.dbo.View1'.

It is attempting to create View2 before View1 exists.

Is this a known issue? In model.xml it looks like the 'sqlpackage export' has written the correct dependencies.

Is it expected that sqlpackage publish should be smart enough to figure out dependencies?

.NET Core - Unable to register data tier application

I'm running into the same issue mentioned here on the Microsoft docs page: https://github.com/MicrosoftDocs/sql-docs/issues/4230

It looks like the registration part is failing in .NET Core, both when using the package (I've tried basically all versions) and when running sqlpackage manually. Here's my code in C#

var connectionString = $"Data Source=tcp:{dbServer}.database.windows.net,1433;Initial Catalog={dbName};User ID={dbUser}@{dbServer};Password={dbPassword};Integrated Security=False;Encrypt=True";
var dacServices = new DacServices(connectionString);
using var dacPackage = DacPackage.Load(dacpacFileStream);
var dacDeployOptions = new DacDeployOptions
{
  CommandTimeout = (int)TimeSpan.FromMinutes(25).TotalSeconds,
  BlockWhenDriftDetected = false,
  DropConstraintsNotInSource = true,
  DropObjectsNotInSource = true,
  DropIndexesNotInSource = true,
  RegisterDataTierApplication = true,
  ScriptDatabaseOptions = false
};
dacServices.Deploy(dacPackage, dbName, true, dacDeployOptions);

The exception I'm getting has an InnerException of type Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException with the message Unable to register data-tier application: Cannot get stream with FileMode.Create, FileMode.CreateNew, FileMode.Truncate, FileMode.Append when access is FileAccess.Read.

The issue seems to be registering in the master table, I've drilled it down to:

var connectionString = $"Data Source=tcp:{dbServer}.database.windows.net,1433;Initial Catalog={dbName};User ID={dbUser}@{dbServer};Password={dbPassword};Integrated Security=False;Encrypt=True";
var dacServices = new DacServices(connectionString);
dacServices.Register(request.DbName, dacPackage.Name, dacPackage.Version, dacPackage.Description);

which still ends with the same exception.

You can reproduce it in the .NET Core version of sqlpackage like this:

sqlpackage /a:publish /sf:.\MyDacpac-version.dacpac /tcs:"Data Source=tcp:dbServer.database.windows.net,1433;Initial Catalog=dbName;User ID=dbUser@dbServer;Password=dbPassword;Integrated Security=False;Encrypt=True" /p:RegisterDataTierApplication=true /d:True

The diagnostics ouput traces this exception:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 0 : 2020-04-02T08:22:48 : System.ArgumentException: Cannot get stream with FileMode.Create, FileMode.CreateNew, FileMode.Truncate, FileMode.Append when access is FileAccess.Read.
   at Microsoft.Data.Tools.Schema.Sql.Packaging.Package.Open(String path, FileMode packageMode, FileAccess packageAccess, FileShare packageShare) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Packaging\Package.cs:line 841
   at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromFile(FileInfo fileInfo, FileMode mode, FileAccess access) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Build\SqlPackage.Artifact.cs:line 251
Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2020-04-02T08:22:48 : Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package.
 ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Unable to register data-tier application: Cannot get stream with FileMode.Create, FileMode.CreateNew, FileMode.Truncate, FileMode.Append when access is FileAccess.Read. ---> Microsoft.Data.Tools.Schema.Sql.Build.SqlPackageException: Cannot get stream with FileMode.Create, FileMode.CreateNew, FileMode.Truncate, FileMode.Append when access is FileAccess.Read. ---> System.ArgumentException: Cannot get stream with FileMode.Create, FileMode.CreateNew, FileMode.Truncate, FileMode.Append when access is FileAccess.Read.
   at Microsoft.Data.Tools.Schema.Sql.Packaging.Package.Open(String path, FileMode packageMode, FileAccess packageAccess, FileShare packageShare) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Packaging\Package.cs:line 841
   at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromFile(FileInfo fileInfo, FileMode mode, FileAccess access) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Build\SqlPackage.Artifact.cs:line 251
   --- End of inner exception stack trace ---
   at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromFile(FileInfo fileInfo, FileMode mode, FileAccess access) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Build\SqlPackage.Artifact.cs:line 281
   at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Open(FileInfo fileInfo, FileAccess access) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Build\SqlPackage.cs:line 71
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentRegistrar.GenerateTemporaryPackage(DacMetadata metadata, SqlSchemaModel model) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentRegistrar.cs:line 224
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentRegistrar.Register(DacMetadata metadata, ModelStorageType modelStorageType, Guid dacInstanceId) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentRegistrar.cs:line 131
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.RegisterSchema(Guid dacInstanceId) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.cs:line 1907
   --- End of inner exception stack trace ---
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.RegisterSchema(Guid dacInstanceId) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.cs:line 1924
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.RegisterSchema(Guid dacInstanceId) in F:\B\16846\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeployment.Controller.cs:line 322
   at Microsoft.SqlServer.Dac.DeployOperation.<CreateRegisterOperation>b__22_0(Object operation, CancellationToken token) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 304
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\Operation.cs:line 72
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\ReportMessageOperation.cs:line 44
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16846\6200\Sources\Product\Source\DeploymentApi\DeployOperation.cs:line 470

followed by this is the error output of the console:

*** Could not deploy package.

Unable to register data-tier application: Cannot get stream with FileMode.Create, FileMode.CreateNew, FileMode.Truncate, FileMode.Append when access is FileAccess.Read.
Cannot get stream with FileMode.Create, FileMode.CreateNew, FileMode.Truncate, FileMode.Append when access is FileAccess.Read.

Any idea what's going on here?

DacFx .NET Core Support

Are there any plans to support .NET Core in the near future? Right at the moment the nuget package restore prints Package 'Microsoft.SqlServer.DacFx.x64 140.3881.1' was restored using '.NETFramework,Version=v4.6.1' instead of the project target framework '.NETCoreApp,Version=v2.0'

Cannot get rule samples dll to load in Visual Studio 2017

I have tried to run the rule samples dll in VIsual Studio 2017, but I cannot get it to work. I think I changed the references and the directory correctly, but opening an SSDT project does not show the sample rules in the code analysis window. Is there something special needed for Visual Studio 2017 and is there something I can do to find out what is going wrong?

DacFx .NET Core generate .dacpac without Origin.xml

I managed to generate a .dacpac file programmatically from a model on Ubuntu 18.04 with .NET core 2.2 but the file came without the Origin.xml file, am I doing something wrong?

using System;
using System.IO;
using Microsoft.SqlServer.Dac.Model;
using Microsoft.SqlServer.Dac;

namespace dacpacPOC
{
    class Program
    {
        static void Main(string[] args)
        {
            var script = "CREATE SCHEMA [Test]\r\nGO\r\n" +
                "CREATE TABLE [Test].[Identity]([TestId] int NOT NULL IDENTITY,\r\n" +
                "CONSTRAINT [PK_Identity] PRIMARY KEY ([TestId]))\r\nGO\r\n";
            var tSqlModelOptions = new TSqlModelOptions
            {
                AnsiNullsOn = true,
                Collation = "Latin1_General_CI_AI",
                CompatibilityLevel = 130,
                QuotedIdentifierOn = true
            };
            var outputFilename = "Identity.dacpac";
            using (var tSqlModel = new TSqlModel(SqlServerVersion.Sql130, tSqlModelOptions))
            {
                tSqlModel.AddObjects(script);
                if (File.Exists(outputFilename))
                {
                    File.Delete(outputFilename);
                }
                DacPackageExtensions.BuildPackage(outputFilename, tSqlModel, new PackageMetadata { Name = outputFilename }, new PackageOptions { });
            }
        }
    }
}

My .csproj file is the following:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.DACFx" Version="150.4384.2-preview" />
  </ItemGroup>

</Project>

Publishing a DACPAC to a database ignores master.dacpac same directory

Introduction

My solution makes use of the Microsoft.SqlServer.DacFx.x64 (v150.4826.1) NuGet-package to programmatically publish a DACPAC, built using Visual Studio 2019 (Version 16.6.2) and SQL Server Data Tools (16.0.62006.03190), to an existing database.

The DACPAC references types contained in the master.dacpac file which is referenced in the model.xml file as follows:

<CustomData Category="Reference" Type="SqlSchema">
	<Metadata Name="FileName" Value="C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\COMMUNITY\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\100\SQLSCHEMAS\MASTER.DACPAC" />
	<Metadata Name="LogicalName" Value="master.dacpac" />
	<Metadata Name="ExternalParts" Value="[master]" />
	<Metadata Name="SuppressMissingDependenciesErrors" Value="True" />
</CustomData>

Before starting the publish operation, both DACPACs are copied to the same directory:

C:\MyDirectory\MyDatabase.dacpac
C:\MyDirectory\master.dacpac

Issue

When publishing the DACPAC using C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe, the master.dacpac of the same directory (MyDirectory) is used and the operation works.

But when publishing the same file using the DacFx assemblies, they try to load master.dacpac from exactly the same directory as specified in the reference definition in the metadata (C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\COMMUNITY\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\100\SQLSCHEMAS\MASTER.DACPAC) which of course doesn't exist on a production machine.
Thus the operation failes.

25.06.2020 22:59:48 - E - Error SQL0: The reference to external elements from the source named 'master.dacpac' could not be resolved, because no such source is loaded.
25.06.2020 22:59:48 - E - Warning SQL72025: No file was supplied for reference master.dacpac; deployment might fail. When package was created, the original referenced file was located C:\PROGRAM FILES (X86)\MICROSOFT VISUAL STUDIO\2019\COMMUNITY\COMMON7\IDE\EXTENSIONS\MICROSOFT\SQLDB\EXTENSIONS\SQLSERVER\100\SQLSCHEMAS\MASTER.DACPAC.
25.06.2020 22:59:48 - E -  ---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: An error occurred while adding references.  Deployment cannot continue.
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.ThrowIfErrors(String message, ErrorManager errors, Object category)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointPackage.DeploymentEndpointReferenceLoader.OnLoaded(ErrorManager errors)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.DacpacHeaderLoader.Load(ErrorManager errors)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointPackage.OnLoad(ErrorManager errors, DeploymentEngineContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.PrepareModels()
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator()
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(Action`1 msgHandler)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
25.06.2020 22:59:48 - E -    --- Ende der internen Ausnahmestapelüberwachung ---
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
25.06.2020 22:59:48 - E -    bei Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
25.06.2020 22:59:48 - E -    bei Microsoft.SqlServer.Dac.DacServices.InternalPublish(DacPackage package, String targetDatabaseName, PublishOptions publishOptions, Boolean executePlan)

(Timestamps were added by my application)

How to solve this?

Is there a way to specify additional directories, where the DacFx assemblies look for referenced DACPACs?
Is there something like a "reference resolving event" that can be handled by my application returning the path or a stream to the referenced DACPAC?
Are there any other options, that make the DacFx assemblies incorporate the source DACPAC's directory when searching for referenced DACPACs?

Is there anything else that SqlPackage.exe does additionally which makes the reference discovery work and which I could also add to my application?

Thank you for your help!

Updated docs?

Where am I able to find the latest docs?

I can see a new release of Microsoft.SqlServer.DACFx, 150.4573.2, went out a month ago on nuget, but the latest on docs.microsoft.com is 140.3881.1.

Additionally, is Microsoft.Data.Tools.Msbuild likely to be updated with a newer sqlpackage.exe?

Exception when extracting a database into a DACPAC as impersonated user

Introduction

My solution makes use of the Microsoft.SqlServer.DacFx.x64 (v150.4826.1) NuGet-package to programmatically extract a database into a DACPAC, built using Visual Studio 2019 (Version 16.6.2) and SQL Server Data Tools (16.0.62006.03190).

The application itself gets started by the interactively logged on user on Windows under the users domain account.
Let's call this user User-A.
Internally, the application logs on another Windows domain account (let's call it User-B) by calling LogonUser and impersonates the user-context before executing the DacFx assemblys extract method.
After the extract operation, the user-context is reverted back to User-A.

Issue

When executing the extract operation (DacServices.Extract), the following exception gets thrown:

18.05.2020 12:23:44 - E - Microsoft.SqlServer.Dac.DacServicesException: Could not save package to file. ---> Microsoft.Data.Tools.Schema.Sql.Build.SqlPackageException: Der Typeninitialisierer für "MS.Utility.EventTrace" hat eine Ausnahme verursacht. ---> System.TypeInitializationException: Der Typeninitialisierer für "MS.Utility.EventTrace" hat eine Ausnahme verursacht. ---> System.Security.SecurityException: Der angeforderte Registrierungszugriff ist unzulässig.
18.05.2020 12:23:44 - E -    bei System.ThrowHelper.ThrowSecurityException(ExceptionResource resource)
18.05.2020 12:23:44 - E -    bei Microsoft.Win32.RegistryKey.OpenSubKey(String name, Boolean writable)
18.05.2020 12:23:44 - E -    bei Microsoft.Win32.RegistryKey.OpenSubKey(String name)
18.05.2020 12:23:44 - E -    bei Microsoft.Win32.Registry.GetValue(String keyName, String valueName, Object defaultValue)
18.05.2020 12:23:44 - E -    bei MS.Utility.EventTrace.IsClassicETWRegistryEnabled()
18.05.2020 12:23:44 - E -    bei MS.Utility.EventTrace..cctor()
18.05.2020 12:23:44 - E -    --- Ende der internen Ausnahmestapelüberwachung ---
18.05.2020 12:23:44 - E -    bei MS.Utility.EventTrace.EasyTraceEvent(Keyword keywords, Event eventID)
18.05.2020 12:23:44 - E -    bei System.IO.Packaging.Package.Open(Stream stream, FileMode packageMode, FileAccess packageAccess, Boolean streaming)
18.05.2020 12:23:44 - E -    bei Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromStream(Stream stream, FileMode mode, FileAccess access)
18.05.2020 12:23:44 - E -    --- Ende der internen Ausnahmestapelüberwachung ---
18.05.2020 12:23:44 - E -    bei Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromStream(Stream stream, FileMode mode, FileAccess access)
18.05.2020 12:23:44 - E -    bei Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact..ctor(Stream stream, FileAccess access)
18.05.2020 12:23:44 - E -    bei Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Open(Stream stream, FileAccess access)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.DacPackage.Save(Stream stream, DataSchemaModel model, DacMetadata metadata, Nullable`1 minModelVersion)
18.05.2020 12:23:44 - E -    --- Ende der internen Ausnahmestapelüberwachung ---
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.DacPackage.Save(Stream stream, DataSchemaModel model, DacMetadata metadata, Nullable`1 minModelVersion)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.DacServices.<>c__DisplayClass65.<>c__DisplayClass67.<CreateExportOperation>b__63()
18.05.2020 12:23:44 - E -    bei Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.DacServices.<>c__DisplayClass65.<CreateExportOperation>b__62(Object operation, CancellationToken token)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.DacServices.Extract(Func`1 streamGetter, String databaseName, String applicationName, Version applicationVersion, String applicationDescription, IEnumerable`1 tables, DacExtractOptions extractOptions, Nullable`1 cancellationToken, DacLoggingContext extractLoggingContext)
18.05.2020 12:23:44 - E -    bei Microsoft.SqlServer.Dac.DacServices.Extract(Stream packageStream, String databaseName, String applicationName, Version applicationVersion, String applicationDescription, IEnumerable`1 tables, DacExtractOptions extractOptions, Nullable`1 cancellationToken)

(Timestamps were added by my application)

If I launch the application as User-B and execute the extract operation without the additional impersonation, everything works fine.

How to solve this?

When tracking down this issue, I found out, that even if I perform several actions to fully load the profile of User-B, something still seems to be missing so I can't get it to work.
Finally I modified the workflow so my application gets directly launched as User-B rather than as User-A by using CreateProcessWithLogon, which basically works now. But I'm not very satisfied with this solution.

What does MS.Utility.EventTrace do during it's initialization that's missing after LogonUser?
And what has to be done to support executing DacFx actions in an impersonated user-context?

Thank you for your help!

Feature Request: Contributors, add parameter to allow load path to be passed in

When we load deployment contributors, the path they are loaded from is pretty esoteric (a subfolder of the sqlpackage.exe that is currently executing or a known path in "c:\program files (x86)") this makes deploying contributors almost impossible without admin rights.

Please give us a parameter we can use like /p:DeploymentContributorLoadPath=c:\blah;c:\blah\blah wich will be search locations for deployment contributors.

thanks!

How to add DML scripts to DacPackageExtensions.BuildPackage(...)

Greetings,

I am having a hard time understanding how to generate a .dacpac file with post execution scripts with the DacFx and DacPackageExtensions.

I can't seem to get a valid .dacpac output that contains my DML scripts.

Here is my base .dacpac generating class, the rest of the project is zipped and attached here

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Dac.Model;
using Microsoft.SqlServer.Dac;
using System.Text;
using static System.Environment;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace Pokemon.DacpacConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Begining scripts generation...");
            var dacpacScripts = GenerateDacCreateScriptList();
            Console.WriteLine("Finished generating scripts.");

            var tSqlModelOptions = new TSqlModelOptions
            {
                AnsiNullsOn = true,
                Collation = "Latin1_General_CI_AI",
                CompatibilityLevel = 150,
                QuotedIdentifierOn = true
            };

            var outputFilename = "pokemon.dacpac";

            Console.WriteLine("Begining .dacpac generation...");
            using (var tSqlModel = new TSqlModel(SqlServerVersion.Sql150, tSqlModelOptions))
            {
                foreach(var script in dacpacScripts)
                    tSqlModel.AddObjects(script);

                var postDeployScripts = GenerateDacPostDeploymentScript();
                foreach(var script in postDeployScripts)
                    tSqlModel.AddOrUpdateObjects(
                        inputScript: script.Value,
                        sourceName: script.Key,
                        options: new TSqlObjectOptions {
                            AnsiNulls = true,
                            QuotedIdentifier = true
                        });
                                
                if (File.Exists(outputFilename))
                    File.Delete(outputFilename);
                    
                DacPackageExtensions.BuildPackage(
                    outputFilename, 
                    tSqlModel, 
                    new PackageMetadata { Name = outputFilename, Version = "poke-1.0.0" }, 
                    new PackageOptions {  });
            }
            Console.WriteLine("Finished generating .dacpac, check output folder.");
        }

        private static List<string> GenerateDacCreateScriptList()
        {
            var tableFiles = Directory
                .GetFiles(
                    Environment.GetEnvironmentVariable("TABLE_SCRIPTS_PATH"), 
                    "*.sql",  
                    new EnumerationOptions() { RecurseSubdirectories = true, IgnoreInaccessible = true });

            var sqlScripts = new List<string>();
            
            foreach(var file in tableFiles)
            {
                Console.WriteLine("Found table file: " + file);
                sqlScripts.Add(File.ReadAllText(file));
            }

            return sqlScripts;
        }

        private static IDictionary<string, string> GenerateDacPostDeploymentScript()
        {
            var postScriptFiles = Directory
                .GetFiles(
                    Environment.GetEnvironmentVariable("POST_EXECUTION_SCRIPTS_PATH"), 
                    "Insert*.sql",  
                    new EnumerationOptions() { RecurseSubdirectories = true, IgnoreInaccessible = true });

            var scripts = new Dictionary<string, string>();

            foreach(var file in postScriptFiles)
            {
                Console.WriteLine("Found post execution script file: " + file);
                scripts.Add(file, File.ReadAllText(file));
            }

            return scripts;
        }
    }
}

How to install Extension into Visual Studio by VSIX?

How can I install my extensions into Visual Studio by VSIX package? I can set that my package has dependency to SSDT extension package. But is there any possibility to add some files to SSDT extension path?

Using DACPAC file to analyze database dependencies

I am looking at using a DacPac approach to reverse engineer a live database.
https://social.msdn.microsoft.com/Forums/vstudio/en-US/49e0f0aa-72e5-46c6-91e0-7dbffbf21bd6/sql-dependency-walk-like-javadoc-or-doxygen-but-for-sql-server?forum=ssdt

For this I have setup a demo database using this script:
https://github.com/Dirkster99/NextLevel/wiki/Create-INTERFACE-Database
...and followed this workflow to create a DacPac file:
https://github.com/Dirkster99/NextLevel/wiki/Create-DacPac-from-Demo-Database

My problem is that the DacPac file is not created because the schema contains references to objects
(stored procedures) that are not contained in the database. Now its easy to work around this issue by
removing these invalid references but some live databases that came into this world without an
engineering process do contain such things :-( ...

So, I am wondering if it is possible to convert the error message into a warning and create the DacPac
file anyway? Maybe, there is a command line tool option or something else that gives me a chance to
extract the database into a DacPac file even if the database contains errors?

I have downloaded the project at www.github.com/Microsoft/DacExtensions and got it to compile and run since the documentation on the pre-requisites is quit good but I cannot find a sample that lets me connect to a database, download and analyze the schema (from a live database). What am I missing? Could you please refer me to a specific sample/option to get me on my way?

Missing Microsoft.SqlServer.DacFx.x86 package for DAC tools version 150

Not sure this is the right place/repo for this issue, but it is the closest one I can find. Hopefully someone can route this to the right place/person/team...

I have a SQL Azure Database deployment that depends on custom DeploymentPlanModifier. It references this NuGet package: https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x86/140.3881.1

Well, now our build agents and Azure Database supports compatibility level 150 so our release process is using the 150 DAC tools (located in C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150 for us). This version of the tools won't load our custom plan modifier.

We get this error:

Error Deploy72002: DeploymentContributor 'XXX' threw exception. Message is: 'Could not load file or assembly 'Microsoft.Data.Tools.Schema.Sql, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)'
DeploymentContributor 'XXX' threw exception. Message is: 'Could not load file or assembly 'Microsoft.Data.Tools.Schema.Sql, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)'
Could not load file or assembly 'Microsoft.Data.Tools.Schema.Sql, Version=14.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

All the assemblies I need are in the DAC 150 tools folder, but this isn't 2001 where we copy DLLs around manually. Give me an updated NuGet package.

DacServices.ExportBacpac: Intermittent errors when extracting a package from a database with numbered stored procedures

DacFX.x64 version: 150.4897.1
SQL Server: SQL Server 2017 (v14.0.2027.2)
Database compatibility level: 110
Data Migration Assistant report: No migration blockers
DacExportOptions: [CommandTimeout: 0s, SchemaModelStorage: File, TargetEngineVersion: Latest, VerifyFullTextDocumentTypesSupported: True, LongRunningCommandTimeout: 0s, DatabaseLockTimeout: 60s, IgnoreIndexesStatisticsOnEnclaveEnabledColumns:False, TempDirectoryForTableData: '']

Sample error message: "Error SQL71501: Error validating element [dbo].[hideObject]: Procedure: [dbo].[hideObject] has an unresolved reference to object [dbo].[checkRights].[1]."

Please note that the numbered stored procedure checkRights;1 exists, the caller proc
stdout_bug_export.txt
edure uses the syntax exec "checkRights;1" (n.b., no dbo-prefix) and that the task fails only intermittently on the database to be exported and other databases with the same schema.

Advice or direction on merging and multiplatforming dacpacs

Is there any advice for how to most efficiently merge a set of dacpacs together into one resulting dacpac?

When publishing a set of 5 dacpacs consecutively to an existing database, the execution seems to take a long time (7-10 minutes). If I publish those 5 to a new database and then extract a composite dacpac from that db, the composite dacpac takes only about 2-3 minutes to publish compared to the 5 individuals. The creation of the composite still takes a long time. Is there a faster or more efficient way to merge or publish multiple dacpacs?

EDIT: original question was posted here with a better example

On a similar theme, is there a faster way for creating several dacpac's targeting different versions of SQL server from one project other than changing the DSP and recompiling?

DAC incorrectly sets containment type = Partial when restoring to SQL 2019 instance

I'm generating a bacpac file from a SQL Azure hosted database (compatibility level SQL Server 2017) and restoring it to a SQL Server localdb instance. This was working fine, but then I upgraded my localdb instance to SQL 2019, and now the restore fails:

Creating deployment plan
Initializing deployment
Verifying deployment plan
Analyzing deployment plan
Importing package schema and data into database
Updating database
*** Error importing database:Could not import package.
Error SQL72014: .Net SqlClient Data Provider: Msg 12824, Level 16, State 1, Line 5 The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database.  You may need to use RECONFIGURE to set the value_in_use.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
                CONTAINMENT = PARTIAL
            WITH ROLLBACK IMMEDIATE;
    END


Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.      Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
                CONTAINMENT = PARTIAL
            WITH ROLLBACK IMMEDIATE;
    END

The important point to note here is that the SQL Azure database has containment type = None so I have no idea why (what I presume to be) the generated script is trying to alter the destination DB to have CONTAINMENT = PARTIAL. I believe that SQLPackage is assuming that a 2019 DB must always be set to have containment type = Partial, rather than using the source database's containment type - i.e. this is a bug.

The arguments I'm passing to SQLPackage are simple: /a:Import /sf:"${bacpacFile}" /tcs:"${targetConnectionString}". This happens with both the latest .NET Framework (15.0.4627.2) and .NET Core (15.0.4630.1) versions of SQLPackage.

Cant install either of the DacFx packages

No reason given other than the error below.

It would be nice if you could list the platform dependencies in the nupkg so we can know what is required to install this. I had to download it manually from the website as Save-Package cant find it to download a copy.

contact the package author

It would also be nice if the package actually listed the project page these extensions go to or the actual contact info for the group @msft that supports this , so I didnt have to hunt for this one, which is probably not the right one, but the closest I can find.

Install-Package : Could not install package 'Microsoft.SqlServer.DacFx.x64 140.3745.1'. You are trying to install this package into a project that targets '.NETFramework,Version=v4.5.2', but the package does not contain any assembly references or content 
files that are compatible with that framework. For more information, contact the package author.
At line:1 char:1
+ Install-Package Microsoft.SqlServer.DacFx.x64
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Install-Package], Exception
    + FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PackageManagement.PowerShellCmdlets.InstallPackageCommand

Strongly typed API - missing Indexes info

Having just implemented a EF Core DatabaseModel creator using the strongly typed API, I was missing strongly type info for table indexes - is this a bug or by design?

microsoft / dacextensions Goto Github PK

dacextensions's Introduction

DACExtensions

Project Description

Background Information

Prerequisites

Usage

Samples Installation

Code of Conduct

dacextensions's People

Contributors

Stargazers

Watchers

Forkers

dacextensions's Issues

Problem

Change Script Result:

Testing

Introduction

Issue

How to solve this?

Introduction

Issue

How to solve this?

Recommend Projects

Recommend Topics

Recommend Org