Manually modifying a SQL Server *.bacpac file prior to importing

I encountered a situation where I needed to export a data-tier application (i.e. *.bacpac) from an Azure SQL Database and import it into a local instance of SQL Server. The *.bacpac created just fine; however, during import, it failed with a collation error. For various reasons, I did not want to modify the Azure SQL database and regenerate the *.bacpac. Instead, I found this helpful post:

http://inworksllc.com/editing-sql-database-azure-bacpac-files/

Those instructions utilize a file called dacchksum.exe that is available at:

https://github.com/gertd/dac/tree/master/drop/debug

Here is the process that I used:

• Unzip the *.bacpac file (I use 7-zip, but any normal zip tool should work)
• Open the model.xml file
• Find the offending stored procedure, comment the body of the procedure, and add a return statement
• Save and close model.xml
• Zip all of the content into a new *.zip
• Rename the new *.zip into a *.bacpac (e.g. newfile.bacpac)
• Run the following command:
  dacchksum.exe /i:newfile.bacpac
• Mark and copy the new checksum value
• Open Origin.xml (from the unzipped folder)
• Find the checksum (towards the bottom of the file) and replace it with the new checksum
• Save and close Origin.xml
• Zip all of the content into a new *.zip
• Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
• Import the new *.bacpac (using SQL Server Management Studio)

This process was successful ... except that importing a *.bacpac fails on the first error (it does not report all errors on the first run). After I fixed the first import error, it failed a second time on a different error, so I had to repeat the process multiple times. In my case, the *.bacpac was over 750 MB, so zipping everything multiple times was taking several seconds each time. I wondered if there was a more efficient process (where I did not need to zip, compute checksum, alter checksum, then zip again). I opened dacchksum.exe with ILSpy and found that the checksum is just a SHA256 checksum. Conveniently, PowerShell has a Get-FileHash cmdlet that does exactly that. Using Get-FileHash instead of dacchksum.exe, the revised steps are as follows:

• Unzip the *.bacpac file
• Open the model.xml file
• Find the offending stored procedure, comment the body of the procedure, and add a return statement
• Save and close model.xml
• Run the following PowerShell command:
• Get-FileHash fullpathto\model.xml | Format-List
• Copy the new checksum value
• Open Origin.xml
• Find the checksum (towards the bottom of the file) and replace it with the new checksum
• Save and close Origin.xml
• Zip all of the content into a new *.zip
• Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
• Import the new *.bacpac (using SQL Server Management Studio)

This process was also successful, and was quicker because I only had to zip the content once.