Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: The Element or Annotation class SqlIndex does not contain the Property class Online

Question

Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: The Element or Annotation class SqlIndex does not contain the Property class Online

jackliusr opened this issue 9 months ago · 56 comments

jackliusr commented 9 months ago

SqlPackage or DacFx Version: 162.2.111.2
.NET Framework (Windows-only) or .NET Core: 8.0.2
Environment (local platform and source/target platforms): wsl2 + ubuntu22.04

Steps to Reproduce:

import a dacpac

(DacFx/SqlPackage/SSMS/Azure Data Studio)

Answer 1 · 2024-03-13T14:39:00.000Z

Can you share a bit more about how the dacpac was created?

Answer 2 · 2024-03-13T15:49:02.000Z

I run below command to build and get the dacpac file.

dotnet build ddd.sqlproj

ddd.sqlproj

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" ToolsVersion="4.0">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.15-preview" />

Answer 3 · 2024-03-13T15:52:15.000Z

Please double check your sqlpackage version

Answer 4 · 2024-03-14T01:07:26.000Z

Below is the version info, command I used to get, and location of the sqlpackage.

jack@DIM-I-1HVZJR3:~$ sqlpackage /version
162.2.111.2
jack@DIM-I-1HVZJR3:~$ sqlpackage /version
162.2.111.2
jack@DIM-I-1HVZJR3:~$ which sqlpackage
/home/jack/.dotnet/tools/sqlpackage
jack@DIM-I-1HVZJR3:~$

Answer 5 · 2024-03-14T07:47:27.000Z

Running into the same issue. There is very little logging about what's actually going wrong

It's happening since updating visual studio to the latest version on our azure pipelines agent image. All our database builds are now failing

Answer 6 · 2024-03-14T12:49:38.000Z

Same issue: In the generated .dacpac file we now have a model.xml having with a property 'online' which causes the error message. Sample:

<Element Type="SqlIndex" Name="[dbo].[Core_Contact].[IX_Core_Contact]">
	<Property Name="Online" Value="False" />
	<Relationship Name="ColumnSpecifications">
		<Entry>
			<Element Type="SqlIndexedColumnSpecification">
				<Relationship Name="Column">
					<Entry>
						<References Name="[dbo].[Core_Contact].[Id]" />
					</Entry>
				</Relationship>
			</Element>
		</Entry>
		<Entry> etc.

If this line with the 'Online' property does not exist: No problem. `

Using VS 17.9.3: This line exists.
Using VS 17.9.2: This line does not exist.

The line is written, if you define the index with the option 'ONLINE'. Sample:

CREATE NONCLUSTERED INDEX [IX_Core_Contact] ON [dbo].[Core_Contact]
(
    [Id] ASC,
    [Mandator_Id] ASC
)
INCLUDE ( [ShortName]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Answer 7 · 2024-03-14T13:19:52.000Z

@Thomas386 great work sherlock. This will probably solve the issue for us. We have exactly 1 index in the project with the online property set

nevertheless, this should be fixed

Answer 8 · 2024-03-14T18:00:22.000Z

@Thomas386 is the version of sqlpackage from AzurePipelin agent? This issue only happens if the version sqlpackage used to deploy is older than the version of dacfx used in SQLProject. can you confirm that you get the bug if you try to deploy locally ( not from Azure Pipeline) using sqlpcakge 162.2.111.2? Azure pipeline agents are usually have delay on updating to latest version of sqlpcakge.

Answer 9 · 2024-03-14T18:24:15.000Z

Isn't sql package update included in the visual studio updates? We started seeing the issue after updating visual studio on our own custom image for azure pipelines. Wouldn't this update both sqlproject and sqlpackage?

Answer 10 · 2024-03-14T18:25:28.000Z

We had the same issue crop up today after we patched our build server's Visual Studio to the latest version. Updating our deployer to use the latest 162.2.111 SqlPackage fixed the issue.

Answer 11 · 2024-03-14T18:40:49.000Z

@PaulVrugt when using Azure pipeline for deploy, the sqlpckage is coming from the agent not from Visual Studio and usually there's a delay between when we ship a new sqlpckage and when the agents get the update. VS also uses DacFx to build the project so in your case, you used DacFx 162.2 to build the project, because you updated VS but to deploy you are using 162.1 version of sqlpackge which is coming from Azure pipeline agent.

Answer 12 · 2024-03-14T19:14:36.000Z

We had the same issue crop up today after we patched our build server's Visual Studio to the latest version. Updating our deployer to use the latest 162.2.111 SqlPackage fixed the issue.

@jgillette - When you say upgraded the deployer, are you referring to the Azure DevOps build agent itself to the latest version?

Answer 13 · 2024-03-14T20:01:41.000Z

if using azure pipeline and not using self-hosted agent, you can check the version of image picked by you pipeline and see if it matches the latest image here https://github.com/actions/runner-images?tab=readme-ov-file#faqs

Answer 14 · 2024-03-14T20:14:48.000Z

@PaulVrugt when using Azure pipeline for deploy, the sqlpckage is coming from the agent not from Visual Studio and usually there's a delay between when we ship a new sqlpckage and when the agents get the update. VS also uses DacFx to build the project so in your case, you used DacFx 162.2 to build the project, because you updated VS but to deploy you are using 162.1 version of sqlpackge which is coming from Azure pipeline agent.

But we are using a private agent with the latest visual studio installed. We are not using hosted agents. There is no delay because there is only 1 thing (the agent image) to update. Apparently the latest vs update does not include an update of sqlpackage

Answer 15 · 2024-03-14T20:16:29.000Z

@PaulVrugt are you using sqlpackage from VS location? would you please send the path? it's possible that you need to update sqlpackage using https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16 in your agent

Answer 16 · 2024-03-15T07:01:55.000Z

@llali : Thx for your help. -> We are on old versions and we will fix this now.

Answer 17 · 2024-03-15T08:28:59.000Z

We are using the SqlAzureDacpacDeployment@1 in azure pipelines, which apparently uses the standalone version of sqlpackage in C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe and not the vs included one in:
C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe

this is where the issue occurs. It would have been a bit better if the vs included one was used. Anyway, we know the cause now

Answer 18 · 2024-03-18T13:17:26.000Z

We are using the SqlAzureDacpacDeployment@1 in azure pipelines, which apparently uses the standalone version of sqlpackage in C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe and not the vs included one in: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe

this is where the issue occurs. It would have been a bit better if the vs included one was used. Anyway, we know the cause now

The reason why the step isn't able to pick up the sqlpackage.exe in the Visual Studio installation folder is probably located here:
https://github.com/microsoft/azure-pipelines-extensions/blob/ae1eb2b556298beb949fc61e885e55fc18abed07/TaskModules/powershell/TaskModuleSqlUtility/SqlPackageOnTargetMachines.ps1#L327

Debugging this showed it is looking for the path: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe\SqlPackage.exe

I guess the folder structure has changed in any of the visual studio versions.

Answer 19 · 2024-03-18T16:19:19.000Z

We are using the SqlAzureDacpacDeployment@1 in azure pipelines, which apparently uses the standalone version of sqlpackage in C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe and not the vs included one in: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe
this is where the issue occurs. It would have been a bit better if the vs included one was used. Anyway, we know the cause now

The reason why the step isn't able to pick up the sqlpackage.exe in the Visual Studio installation folder is probably located here: https://github.com/microsoft/azure-pipelines-extensions/blob/ae1eb2b556298beb949fc61e885e55fc18abed07/TaskModules/powershell/TaskModuleSqlUtility/SqlPackageOnTargetMachines.ps1#L327

Debugging this showed it is looking for the path: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe\SqlPackage.exe

I guess the folder structure has changed in any of the visual studio versions.

haha that simply looks like a bug. It's using the SqlPackage.exe double

Answer 20 · 2024-03-18T16:22:08.000Z

I think the debugging conclusion is wrong.

Answer 21 · 2024-03-18T16:28:34.000Z

Well the part I posted is iterating through all elements in the DAC folder and appending sqlpackage.exe. There are no subfolders in the DAC folder for VS 2022. Which results in test-path calls for paths like the one I mentioned.

Answer 22 · 2024-03-18T16:41:33.000Z

@b3go I see! It used to be in a number folder under the VS folder...

Answer 23 · 2024-03-18T16:44:13.000Z

@dzsquared FYI! Do you take a PR to fix this?

Answer 24 · 2024-03-19T18:49:45.000Z

We encountered this issue today with the latest version of the windows-latest VM. Updating our local release agents to use the newest version of sqlpackage resolved the issue.

Answer 25 · 2024-03-20T17:35:20.000Z

Pulling latest SQL package fixed this for us. Thanks

Answer 26 · 2024-03-21T12:24:01.000Z

That doens't seem to work for me. I updated to visual studio 17.9.4 and SqlPackage to 162.2.111 from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16, but still getting the same error

Answer 27 · 2024-03-21T12:30:53.000Z

@dieterQBIT In which context are you geeting the error?

Answer 28 · 2024-03-21T12:42:49.000Z

@dieterQBIT That does not look like the same error ?? More like file not found

Answer 29 · 2024-03-21T12:45:55.000Z

sorry, project wasn't properly built. This is the error I get when trying to deploy

Answer 30 · 2024-03-21T12:48:59.000Z

I just noticed those older versions are also still installed. Should I remove them?

Answer 31 · 2024-03-21T12:58:16.000Z

@dieterQBIT Please share your deployment script/batch file, screenshots are not so helpful

Answer 32 · 2024-03-21T13:03:26.000Z

EasyOffice.deploy_allowdataloss.zip

This worked perfectly until I updated to visual studio 17.9.3

Answer 33 · 2024-03-21T13:36:16.000Z

That doens't seem to work for me. I updated to visual studio 17.9.4 and SqlPackage to 162.2.111 from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16, but still getting the same error

Did you install using dotnet tool install? These are also not getting picked up by the PowerShell. You have to install using the DacFramework.msi

Answer 34 · 2024-03-21T13:38:55.000Z

I used 'Download and run the DacFramework.msi installer for Windows' under Windows (.NET Framework)

Answer 35 · 2024-03-21T13:41:38.000Z

Can you run the pipeline with diagnostics? It will show you which sqlpackage.exe is used.

Answer 36 · 2024-03-21T14:07:34.000Z

It shows Running 64-bit SqlPackage Version 162.0.52.1 on .NET Core
So where does it come from?

Answer 37 · 2024-03-21T14:16:55.000Z

@dieterQBIT .NET global tool?

Try to update with:

dotnet tool update Microsoft.Sqlpackage -g

Answer 38 · 2024-03-21T14:24:25.000Z

This works for me! Thanks a lot!

But how is this possible? I never used .NET Core before.

Answer 39 · 2024-03-21T14:25:06.000Z

Someon or some app installed it

Answer 40 · 2024-03-21T14:29:52.000Z

strange...but again, thanks! case closed for me :-)

Answer 41 · 2024-03-21T23:08:16.000Z

Just started happening to us as well last night. For us it is happening in Azure Devops pipelines when deploying DB updates.

Not happening in Visual Studio on the latest version 17.9.4

Answer 42 · 2024-03-22T05:54:43.000Z

@williambuchanan2 own agent or Microsoft hosted?

Can you share your task ?

Answer 43 · 2024-03-22T05:56:26.000Z

@williambuchanan2 own agent or Microsoft hosted?

Can you share your task ?

Microsoft hosted. Had to remove all the "ONLINE =..." from our DB project to get it to deploy.

Answer 44 · 2024-03-22T05:57:44.000Z

@williambuchanan2 can you share the task yaml snippet?

Answer 45 · 2024-03-22T06:01:38.000Z

@williambuchanan2 can you share the task yaml snippet?

We aren't using yaml as such. We are using the "SQL Server database deploy" task, but here is the yaml from that:

variables:
PDSDatabaseDacpac: '$(System.DefaultWorkingDirectory)/_Database ($(Release.EnvironmentName))/drop/Database/PDS.Database/bin/Release/PDS.Database.dacpac'
DBServer: 'XXX'
DBUsername: 'xxxx'
DBPassword: 'xxxx'
Args_Mining: '/v:xxx=xxxx /v:CustomerType=Mining /v:LegacyCardinalityEstimation=OFF'

steps:

task: SqlDacpacDeploymentOnMachineGroup@0
displayName: Testing
inputs:
DacpacFile: '$(PDSDatabaseDacpac)'
ServerName: '$(DBServer)'
DatabaseName: XXXX
AuthScheme: sqlServerAuthentication
SqlUsername: '$(DBUsername)'
SqlPassword: '$(DBPassword)'
AdditionalArguments: '$(Args_Mining) /p:BlockOnPossibleDataLoss=false'

Answer 46 · 2024-03-25T15:04:24.000Z

Having same exact error. I'm on VS 2022 17.9.4. If I build with VS 2019 16.11.34, works fine.

Answer 47 · 2024-03-25T15:09:34.000Z

closing this since it's not a dacfx bug

Answer 48 · 2024-03-25T15:20:48.000Z

Resolution
You may need to update SqlPackage on your pipeline agent - https://learn.microsoft.com/sql/tools/sqlpackage/sqlpackage-download

Other notes
It looks like one of the ADO tasks is also not pulling SqlPackage from the correct location. That repo is open to PRs, please feel free to tag me if you're able to jump in to correct before I get around to it. (#427 (comment))

Answer 49 · 2024-03-25T15:30:05.000Z

@b3go Interested in doing a PR to fix the AzDo task bug?

Answer 50 · 2024-03-25T15:40:31.000Z

@dzsquared Happy to do a PR, but what version number should be returned for the dacFX under VS now that there is no folder with a version number? 999? Or open a .dll file in the folder an extract the number?

See the Get-SqlPackageOnTargetMachine method in https://github.com/microsoft/azure-pipelines-extensions/blob/master/TaskModules/powershell/TaskModuleSqlUtility/SqlPackageOnTargetMachines.ps1

Answer 51 · 2024-03-25T16:33:42.000Z

@ErikEJ I guess you meant me :D I could do a PR but I have the same question about the version number. Also the script seems to be unable to locate sqlpackage.exe if it was installed via dotnet tool install. Maybe it would be easier to just take it from an env var? Pipeline Task users could easily change it if they want to use another version.

Btw I opened an issue in the relevant repo microsoft/azure-pipelines-extensions#1225

Answer 52 · 2024-03-26T09:59:57.000Z

I did a PR microsoft/azure-pipelines-extensions#1226

Answer 53 · 2024-04-02T17:56:32.000Z

Hey folks,
I'm struggling to work out what the issue is here in order to resolve it 😄

I have a GitHub Action workflow failing with this error:

The Element or Annotation class SqlIndex does not contain the Property class Online.
At line:97891 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], Exception
    + FullyQualifiedErrorId : dbatools_Publish-DbaDacPackage

Reading through this conversation it seems like it's a mismatch in version of dacfx and sqlpackage.exe? However, I am using the windows-latest image for the runner, the list of installed software states that this includes DacFx 162.2.111.2 and I'm using the latest dbatools module (Publish-DbaDacPackage) which has SqlPackage.exe version 162.2.111.2

I have tried to use the previous version of dbatools\dbatools.library but this gives me the same error.

What am I missing? Thanks in advance!

Answer 54 · 2024-04-02T19:05:18.000Z

@jpomfret - if you add a script step that calls sqlpackage /version, does it return 162.2.111?
Checking the dba tools code quick, I think it uses the msbuild nuget instead of sqlpackage itself - https://github.com/dataplat/dbatools/blob/86b3f3db92ab8f2f6ebab4259d47f7da158a748c/public/Publish-DbaDacPackage.ps1#L69
that nuget package is uh, not updated.

Answer 55 · 2024-04-11T16:55:56.000Z

hey @dzsquared
Really sorry I got side tracked on this - when I run sqlpackage /version I get the following:

sqlpackage : The term 'sqlpackage' is not recognized as the name of a cmdlet, function, script file, or operable 
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At D:\a\_temp\cd2d7e43-4e35-4661-b3fd-6f6a022cd6d2.ps1:8 char:1

I think that dbatools uses the sqlpackage from the library module - but I'm not exactly sure how this works 🤔 I'll reach out to Chrissy on this too..
https://github.com/dataplat/dbatools.library

Answer 56 · 2024-05-14T06:47:59.000Z

This topic shouldn't be closed.
Visual Studio contains a new version of dacfx then https://aka.ms/dacfx-msi

I had to copy this directory for deployment:
C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC
because I couldn't find a standalone download