View BACPAC Files Using This One Weird Trick

I’ve talked before about examining the contents of a DACPAC using the built in ‘Unpack…’ command as shown here:

Unpacking the DACPAC will allow for me to see the contents:

Until recently I did not think it possible to view the contents of a BACPAC file. Last month at SQL Bits an attendee reminded me about this one weird trick to view the contents of a BACPAC file. The trick is like how you examine the XML inside of office documents, we add the .ZIP extension! First, make a copy of the BACPAC file and rename (in case we need a backup):

I will click ‘yes’, and then I can examine what is inside:

I can navigate inside the data folder and find all the tables included in the BACPAC. The snapshot replication BCP process creates the tables within the BACPAC. The easiest way to import the data is to use SSMS and import the BACPAC file as a whole. But, if I wanted to get a subset of the tables I can use the BULK INSERT command like this:

BULK INSERT dbo.DatabaseLog
    FROM 'some-filepath-name-here\TableData-000-00000.BCP'
    WITH (DATAFILETYPE = 'native');

This will allow for me to only insert the tables I want.

I could also automate a process to move data between different databases and systems. SQL Server Replication would be great for this, but replication may not always be the answer. For example, sometimes you need to move data between versions and editions of SQL Server. And then there is the fact that not all data resides in SQL Server (gasp!), so you may need to use some BCP to get the job done.