-
Notifications
You must be signed in to change notification settings - Fork 16
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SqlPackage Exports (bacpacs) are not importable by SSMS or Azure import/export #363
Comments
Tracking down the 140 version of SqlPackage has mitigated the error detailed in the OP. |
Hi @bryanhunwardsen This issue resides in the System.IO.Packaging library and we have opened an issue dotnet/runtime#94899 |
There are a few compatibility issues with packages generated from .NET Core and the .NET Framework version of DacFx. The recommendation is to use the same version for both import/export. SSMS uses the .NET Framework so if you would like to use it for import, please use the sqlpackage.exe installed from the MSI. Worth noting that SSMS doesn't always use the latest version of DacFx so there might be some issues with using an older version for import. Ideally we recommend just using the same sqlpackage for both operations. |
@zijchen, We use SqlPackage to automate the export of an on prem db followed by azopy to Azure Storage. |
Updating here to acknowledge that this has been added to documentation as a known issue:
We are working towards a solution such that bacpacs created with the SqlPackage CLI and other .NET applications can be imported by .NET Framework variants (including the Import/Export capabilities of the Azure portal, Azure PowerShell, and SSMS). Because of the dependency on a fix in .NET, I wanted to be transparent that leveraging a workaround is advised. I'm happy to chat 1:1 with folks on specifics to your environment to find the least ugly solution. (you can reach me on this topic at drskwier@microsoft.com) |
this is fixed in .NET SDK 8.0. Please install the latest runtime v8.0.7 and install sqlpackage with dotnet tool. |
,Net versions installed/available on machine:
PSChildName Version Release
v2.0.50727 2.0.50727.4927
v3.0 3.0.30729.4926
WCF 3.0.4506.4926
WPF 3.0.6920.4902
v3.5 3.5.30729.4926
Client 4.8.04084 528372
Full 4.8.04084 528372
Client 4.0.0.0
.Net Core versions installed/available on machine:
SDK's
7.0.102 [C:\Program Files\dotnet\sdk]
7.0.401 [C:\Program Files\dotnet\sdk]
Runtimes
Microsoft.AspNetCore.App 3.1.28 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 6.0.13 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 6.0.16 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 6.0.22 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 7.0.2 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 7.0.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 7.0.11 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
Microsoft.NETCore.App 3.1.28 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 6.0.13 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 6.0.16 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 6.0.22 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 7.0.2 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 7.0.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.NETCore.App 7.0.11 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
Microsoft.WindowsDesktop.App 3.1.32 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 5.0.17 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 6.0.13 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 6.0.16 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 6.0.22 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 7.0.2 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 7.0.5 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
Microsoft.WindowsDesktop.App 7.0.11 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
-Source (SqlPackage Export)
Windows Server 2016 Datacenter 10.0.14393
Sql Server 14.0.20252.1
Sqlpackage zip/.net tool v162.1.167.1 (tested w/ all prior versions linked above)
-Targets
Windows Server 2016 Datacenter 10.0.14393
-Sql Server 14.0.20252.1
--SSMS 18.4, SSMS 19.1 (Using Import BACPAC - local file system)
--SqlPackage zip/.net tool v162.1.167.1 (tested w/ all prior versions linked above) (Using Import)
Windows 10 19045.3570
-Sql Server 16.0.1000.6
--SSMS 18.12.1, SSMS 19.1 (Using Import BACPAC)
--SqlPackage zip/.net tool v162.1.167.1 (tested w/ all prior versions linked above) (Using Import)
Azure Sql Server 12.0.2000.8
Steps to Reproduce:
Context: We are using Azure DevOps release pipelines leveraging SqlPackage to automate the export of an on-prem database as a bacpac into Azure Blob storage to enable manual (Azure UI) and eventually automated import of the bacpac into Azure Sql Server instances.
_ Export to Azure Blob (Must be done in SSMS 18, this feature is currently broken in SSMS 19.1)
_ Export to Local File System => AZ Copy to Blob
(These base cases would tend to indicate no underlying issues with the source database)
(These base cases would tend to indicate there is no corruption with the SqlPackage generated bacpac)
Error Message from SSMS Import of bacpac that SQLPackage can import but SSMS cannot:
Could not load package from 'C:\MyDatabase.bacpac'. (Microsoft.SqlServer.Dac)
Program Location:
at Microsoft.SqlServer.Dac.BacPackage.Load(String fileName, DacSchemaModelStorageType modelStorageType)
at Microsoft.SqlServer.Management.Dac.DacWizard.CreateDatabaseOnTargetWorkItem.DoWork()
at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()
===================================
File contains corrupted data. (Microsoft.Data.Tools.Schema.Sql)
Program Location:
at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromFile(FileInfo fileInfo, FileMode mode, FileAccess access)
at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Open(FileInfo fileInfo, FileAccess access)
at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Open(String file, FileAccess access)
at Microsoft.SqlServer.Dac.FilePackageSource.Microsoft.SqlServer.Dac.IPackageSource.OpenSqlPackage()
at Microsoft.SqlServer.Dac.DacPackage.ContainsExportedData(IPackageSource packageSource)
at Microsoft.SqlServer.Dac.BacPackage..ctor(IPackageSource packageSource)
at Microsoft.SqlServer.Dac.BacPackage.Load(String fileName, DacSchemaModelStorageType modelStorageType)
===================================
File contains corrupted data. (WindowsBase)
Program Location:
at MS.Internal.IO.Zip.ZipIOLocalFileBlock.Validate(String fileName, ZipIOCentralDirectoryBlock centralDir, ZipIOCentralDirectoryFileHeader centralDirFileHeader)
at MS.Internal.IO.Zip.ZipIOLocalFileBlock.ParseRecord(BinaryReader reader, String fileName, Int64 position, ZipIOCentralDirectoryBlock centralDir, ZipIOCentralDirectoryFileHeader centralDirFileHeader)
at MS.Internal.IO.Zip.ZipIOLocalFileBlock.SeekableLoad(ZipIOBlockManager blockManager, String fileName)
at MS.Internal.IO.Zip.ZipIOBlockManager.LoadLocalFileBlock(String zipFileName)
at MS.Internal.IO.Zip.ZipArchive.GetFile(String zipFileName)
at MS.Internal.IO.Zip.ZipArchive.GetFiles()
at System.IO.Packaging.ZipPackage.ContentTypeHelper..ctor(ZipArchive zipArchive, IgnoredItemHelper ignoredItemHelper)
at System.IO.Packaging.ZipPackage..ctor(String path, FileMode mode, FileAccess access, FileShare share, Boolean streaming)
at System.IO.Packaging.Package.Open(String path, FileMode packageMode, FileAccess packageAccess, FileShare packageShare, Boolean streaming)
at Microsoft.Data.Tools.Schema.Sql.Build.SqlPackage.Artifact.InitializefromFile(FileInfo fileInfo, FileMode mode, FileAccess access)
Error Messages from Azure UI Import or az cli import
The package file provided was not able to be opened. Please run SqlPackage locally to ensure that the file is not corrupt and please ensure that it uploaded to Azure Storage completely.
Based on where the stack trace in the error, a web searches show a possible issue that might be related whereby:
ZipIOLocalFileBlock validation reports corrupted file while System.IO.Compression does not
Lack of useful documentation and useful error reporting has not allowed me to conclude if there is a resident issue with the source database at fault, a database/server/source/target version or framework mismatch combability issue, or if the failing use cases noted above are representative of bugs in sqlpackage or ssms/azure cli api.
Did this occur in prior versions? If not - which version(s) did it work in?
This was not a regression (for us) as this is the first attempt at executing the above work flows that are failing.
All SqlPackage/SSMS versions used are detailed above
(DacFx/SqlPackage/SSMS/Azure Data Studio)
The text was updated successfully, but these errors were encountered: