Dont commit transaction on deploy failure - Synapse

[ashishmg-gds]

Hi,

Im providing the IncludeTransactionalScripts=True in Property groups in .csproj file. But when i check the dacpac, there is no effect . Im trying to achieve a state like below :

1. Execute deploy
2. On error, dont commit the transaction

Im targeting Azure Synapse database, Dont know if its synapse support issue or not.

[jeffrosenberg]

~~I don't think that property does what you're thinking, unfortunately. DacFX / SqlPackage doesn't manage transactions generally, and I don't think that property does anything to set a transaction.~~

~~I think your issue may need to be solved outside the scope of our project.~~

EDIT: I wrote this comment just based on the name of the property, but after looking it up I think I may be wrong. I can look into this further after the holidays, but in the meantime I'm leaving the rest of my comment below as a potential workaround.

At a high level, here's what I'd suggest:

• Use SqlPackage to generate (but not execute) a deployment script
• Create a deployment wrapper that can manage a database connection in SQLCMD mode (I know PowerShell supports this, and I'm sure .NET must, not sure about other languages)
• Begin a transaction
• Execute the script within that transaction
• Manage returned errors by rolling back your top-level transaction

[jeffrosenberg]

@ashishmg-gds would you be able to post the csproj file and generated deployment script?

[jeffrosenberg]

@ashishmg-gds are you able to share the .csproj file and/or generated deployment script? I could use some more information to troubleshoot this, so please provide whatever you can; otherwise, I'll close this issue.

[ashishmg-gds]

@jeffrosenberg Apologies for the late reply.

csproj

<Project Sdk="MSBuild.Sdk.SqlProj/1.17.3">
	<PropertyGroup>
		<TargetFramework>netstandard2.0</TargetFramework>
		<SqlServerVersion>SqlDw</SqlServerVersion>
	</PropertyGroup>
	<PropertyGroup>
		<IncludeTransactionalScripts>True</IncludeTransactionalScripts>
	</PropertyGroup>

	<ItemGroup>
		<Content Include="Security\**\*.sql" />
		<Content Include="meta_ctl\Tables\*.sql" />
		<Content Include="meta_ctl\StoredProcedures\*.sql" />
		<Content Include="meta_audit\Tables\*.sql" />
		<Content Include="meta_audit\StoredProcedures\*.sql" />
		<Content Include="dbo\**\*.sql" />
		<Content Include="dq\**\*.sql" />
	</ItemGroup>

</Project>

my profile.xml

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
    <DeployScriptFileName>deploy.sql</DeployScriptFileName>
    <ExcludeUsers>True</ExcludeUsers>
    <ExcludeLogins>True</ExcludeLogins>
    <ExcludeDatabaseRoles>True</ExcludeDatabaseRoles>
    <CreateNewDatabase>False</CreateNewDatabase>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <DoNotDropLogins>True</DoNotDropLogins>
    <DoNotDropUsers>True</DoNotDropUsers>
    <DoNotDropRoleMembership>True</DoNotDropRoleMembership>
    <DoNotDropApplicationRoles>True</DoNotDropApplicationRoles>
    <DoNotDropDatabaseRoles>True</DoNotDropDatabaseRoles>
    <DoNotDropPermissions>True</DoNotDropPermissions>
    <TargetConnectionString>Data Source=xxxxxx;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Integrated Security=False;Trusted_Connection=False</TargetConnectionString>
    <TargetDatabaseName>xxxxx</TargetDatabaseName>
    <TargetResourceGroup>xxxxx</TargetResourceGroup>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

Im unable to provide my sql, but its basically create table, and stored procedures

[jeffrosenberg]

@ashishmg-gds I've been trying to troubleshoot this, but I'm realizing I still need more information. When you say "when i check the dacpac, there is no effect," what do you mean? Are you decompressing the .dacpac file and looking at model.xml? Or does "check the dacpac" mean something else here? Because these are deploy properties and not build properties, I wouldn't expect to see them in the dacpac.

Also, how are you trying to publish? Using our SDK's dotnet publish command, using sqlpackage, or through some other means?

[ashishmg-gds]

@jeffrosenberg Im unzipping and checking the dacpac. Also Im using sqlpackage to publish the dacpac

[jeffrosenberg]

@ashishmg-gds I think this is most likely an Azure Synapse issue, since I was able to validate that this property works correctly for me when doing a local build and using SqlPackage to publish to SQL Server. Here's the script I was able to generate for reference: add-table-inside-transaction.sql.txt

I don't have an instance of Azure Synapse to test with, so I think my next recommendation would be to have you test publishing a small sample database to an instance of both standard SQL Server and Azure Synapse, and see if that explains the issue. If you need a simple project for a jumpstart, you can look at https://github.com/jeffrosenberg/db-test, which I often use for testing issues in this repo. It's nothing special, but could save you a few minutes. You should be able to just use the Base directory for your build.

For the record, if it is an issue with Azure Synapse, that sounds like it would be more of a SqlPackage issue, since the .dacpac file generated by this project wouldn't be changing.

[ErikEJ]

sqlpackage / DacFX issue which may be solved now