Azure SQL DACPAC task keeps failing

[bipindas]

The Azure SQL DACPAC task keeps failing. The tables are successfully updated but the tasks returns an error.
Initializing deployment (Failed)
[debug]System.Management.Automation.RemoteException: *** Could not deploy package.
[error]System.Management.Automation.ParentContainsErrorRecordException: *** Could not deploy package.

[bipindas]

I have the same build & release definition for an older project thats working fine. I tried both the "Microsoft Azure SQL Database V12" and the "Microsoft Azure SQL Database" project types. Both are not working for the newer release definitions.

[aadje]

I'm experiencing the same error on both a local build agent and also on the hosted build agents.

[aadje]

I've tried modifying the SqlAzureDacpacDeployment task it's powershell script by removing the firewall configurations, since I already had a manual firewall rule for my network. I'm still getting the same error.

[Ajay-MS]

@bipindas and @aadje This error is a generic one thrown by sqlPackage.exe due to many possible reasons but main cause of this error is when sqlPackage.exe is not able to make connection with sql Server.

Possible reasons could be :
1. Firewall rule is not being correctly added .So few questions to isolate it .
a. Have you tried this with hosted agent with auto IP-Address selection ?
b. Is there any proxy to your network.
2. Can you add once this firewall rule -> ALL 0.0.0.0 to 255.255.255.255 then execute your task,
a. Delete your this rule after execution of task.
3. Share task logs to ajya@microsoft.com

@bipindas : This error doesn't seems due to mismatch in dacpac version. If it so, error message will be unable to generate deployment plan but you can try with /p:AllowIncompatiblePlatform=true in additional properties to ensure this.

[bipindas]

@Ajay-MS

1. a. Yes I have selected Auto IP-Address
   b. No proxy
2. Tried this as well.

Sent task logs to you email address.

[bipindas]

@Ajay-MS thanks, your suggestion to use the FQDN of the SQL server fixed the issue.

[aadje]

I fixed my issue. I had to use the fully qualified domain name as well.
Another issue was that my dacpac was expecting a variable. This can be fixed by adding a "/variables:MyParam=MyValue" in the Additional SqlPackage.exe Arguments field.

[himanjanpati]

@aadje , i am facing issues while deploying the Azure SQL DACPAC . I am getting the below errors
##[error]System.Management.Automation.ParentContainsErrorRecordException: *** Could not deploy package.
##[error]System.Exception: Task SqlAzureDacpacDeployment failed. This caused the job to fail. Look at the logs for the task for more details.

##[error] at Microsoft.TeamFoundation.DistributedTask.Worker.JobRunner.Run(IJobContext jobContext, IJobRequest job, IJobExtension jobExtension, CancellationTokenSource tokenSource)

I have also added the Additional SqlPackage.exe Arguments field as you have mentioned above it goes like this
/p:IgnoreAnsiNulls=True /p:IgnoreComments=True/variables:MyParam=MyValue

Please let me know where i went wrong .

[bipindas]

@himanjanpati check your script for errors. If you have a pre or post deployement scripts check those as well.

[himanjanpati]

@bipindas thanx for your suggestion but i m not using any SQL script . I m following the steps mentioned in the below link . I am using the DACPAC package for creting the Database in the Azure SQL server . Please have the look .
https://www.visualstudio.com/en-us/docs/release/examples/azure/azure-web-apps-and-sql-db

[Ajay-MS]

@himanjanpati - Can you check if you have added firewall rule correctly , I hope you have followed above steps to isolate if it's a firewall Issue. Verify following details are correct.

1. Is correct FQDN is provided for SQL server.
2. Username and Password are correct.
3. Firewall Rule has been provided with correct IP address.

Still if you face issue you can try command from cmdline one , final command you can get from logs.

If issue still not resolved then you can share logs with us.

[Sandiejat]

@Ajay-MS I'm getting this error message and have tried the steps you have mentioned. I can connect to the SQL Server from my laptop (SSMS installed locally) and browse the databases. It only happens during the release process - Execute Azure SQL: DacpacTask

Is correct FQDN is provided for SQL server- yes. Can connect from the SSMS installed on my laptop
Username and Password are correct - yes.
Firewall Rule has been provided with correct IP address.- Tried allowing all and even disabling the Firewall.

Any ideas what's going on!?

[Ajay-MS]

@Sandiejat
Sorry for replying late on the tread. Can you share debug logs at RM_Customer_Queries@microsoft.com. You can enable debug logs by adding variable System.Debug=true

[DhatyaSri]

i am facing issues while deploying the Azure SQL DACPAC . I am getting the below errors.

*** Could not deploy package.
2023-07-04T11:11:14.6552264Z Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ' '.
2023-07-04T11:11:14.6556024Z Error SQL72045: Script execution error. The executed script:
2023-07-04T11:11:14.6559694Z DECLARE @LV_CUSTOMERSTATUSID AS INT, @LV_ChannelId AS INT;
2023-07-04T11:11:14.6560479Z