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

[jackliusr]

• 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:

1. import a dacpac

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[dzsquared]

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

[jackliusr]

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" />

[ErikEJ]

Please double check your sqlpackage version

[jackliusr]

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:~$

[PaulVrugt]

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

[Thomas386]

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]

[PaulVrugt]

@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

[llali]

@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.

[PaulVrugt]

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?

[jgillette]

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.

[llali]

@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.

[JoshuaMillerNebraskablue]

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?

[llali]

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

[PaulVrugt]

@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

[llali]

@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

[Thomas386]

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

[PaulVrugt]

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

[b3go]

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.

[PaulVrugt]

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

[ErikEJ]

I think the debugging conclusion is wrong.

[b3go]

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.

[ErikEJ]

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

[ErikEJ]

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

[tpressleyhlr]

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.

[bheemvennapureddy]

Pulling latest SQL package fixed this for us. Thanks

[dieterQBIT]

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

[dieterQBIT]

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

[ErikEJ]

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

[dieterQBIT]

EasyOffice.deploy_allowdataloss.zip

This worked perfectly until I updated to visual studio 17.9.3

[b3go]

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

[dieterQBIT]

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

[b3go]

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

[dieterQBIT]

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

[ErikEJ]

@dieterQBIT .NET global tool?

Try to update with:

dotnet tool update Microsoft.Sqlpackage -g

[dieterQBIT]

This works for me! Thanks a lot!

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

[ErikEJ]

Someon or some app installed it

[dieterQBIT]

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

[williambuchanan2]

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

[ErikEJ]

@williambuchanan2 own agent or Microsoft hosted?

Can you share your task ?

[williambuchanan2]

@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.

[ErikEJ]

@williambuchanan2 can you share the task yaml snippet?

[williambuchanan2]

@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'

[ptoutant]

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

[llali]

closing this since it's not a dacfx bug

[dzsquared]

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))

[ErikEJ]

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

[ErikEJ]

@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

[b3go]

@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

[ErikEJ]

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

[jpomfret]

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!

[dzsquared]

@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.

[jpomfret]

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

[ckrueger1979]

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