A Question was posed on Stackoverflow.  How do you create a folder structure from a created DacPac or BacPac.  This article is how I went about doing this with PowerShell.

The first means was to try and find someone who’d tried this before. The best I could find searching was this article: Deploy DACPACs with PowerShell.  This Script does an excellent job of showing how you can use the SQL 2014 DLL’s to create a Script that can than be deployed to your database.   This did not answer the question though.

Thankfully one of the participants in the question was kind enough to show how to do this very thing in C# so I took their pseudo code and turned it into PowerShell Code.

using (TSqlModel modelFromDacpac = new TSqlModel(dacpacPath))
{
 IEnumerable<TSqlObject> allObjects = model.GetObjects(QueryScopes);
 foreach (TSqlObject tsqlObject allObjects)
 {
 string script;
 if (tsqlObject.TryGetScript(out script))
 {
 // Some objects such as the DatabaseOptions can't be scripted out.

// Write to disk by object type
 string objectTypeName = tsqlObject.ObjectType.Name;
 // pseudo-code as I didn't bother writing.
 // basically just create the folder and write a file
 this.MkdirIfNotExists(objectTypeName);
 this.WriteToFile(objectTypeName, tsqlObject.Name + '.sql', script;
 }
 }
}

Starting at the top of the script I need to translate the using Statement into a New-object in PowerShell. In order to do that I needed to find what Dot net class TSQLModel was in.  Based on that research I found that I needed to add the type to my session(Microsoft.SqlServer.Dac.Extensions.dll).  Once the type was added I was then able to get the model from my DacPac.

<br>

add-type -path 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.Extensions.dll'

$model =[Microsoft.SqlServer.Dac.Model.TSqlModel]::new(((get-item ".\$dacpac").fullname))

Now that I have the Model of my dacpac I need to figure out how to make this into a PowerShell piece of code IEnumerable<TSqlObject> allObjects = model.GetObjects(QueryScopes);

I know my return type is IEnumerable Tsql object.. Now the question is how do I query my model and get that return object.  Based on the C# code I need to call GetObjects. GetObjects expects you to pass an object type (QueryScopes variable) and optionally you  can pass the Object Identifier ID or the Object type.   The queryScopes is an enumeration that has the following values(All, Builtin, Default, None, SameDatabase, System, UserDefined). I chose the All so I could see what this method would return.

$returnObjects = $model.GetObjects([Microsoft.SqlServer.Dac.Model.DacQueryScopes]::All)

Now the next step is to step through the return result and test each item to see if it can be scripted.  When calling the TryGetScript it has an output so we must declare a variable before we call this method.

<br>

$s = ''
foreach($r in $returnObjects)
{
 if ($r.TryGetScript([ref]$s))
 {
 $objectTypeName = $r.ObjectType.Name;
 $d="c:\temp\db\$objectTypeName"
 if(!(test-path $d ))
 {
 new-item $d -ItemType Directory
 }
 $filename = "$d\$($r.Name.Parts).sql"

 if(! (test-path $filename))
 {new-item $filename -ItemType File}
 $s | out-file $filename -Force
 write-output $filename
 }

}

I found when I ran this that it would error on creation of a directory.

dactest.ps1 (28, 10): ERROR: At Line: 28 char: 10
ERROR: + $s | out-file $filename -Force
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : OpenError: (:) [Out-File], NotSupportedException
ERROR: + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand
ERROR:

In order to find this exception I decided to put a Try Catch around the act of creating the file:

Try
 {
 new-item $filename -ItemType File
 }
 Catch
 {
 "Filename error $filename"
 }

What I found after putting the Try catch in place was that the exception was be cause the object names were a Url:

Filename error c:\temp\db\Service\http://schemas.microsoft.com/SQL/Notifications/EventNotificationService.sql

In order to fix this I implemented a test to see if the item name was a url

[system.uri]::IsWellFormedUriString(‘http://schemas.microsoft.com/SQL/Notifications/EventNotificationService.sql’, [system.uri]::IsWellFormedUriString)

Now that I know that the filename is a uri I can parse and get the last item in the uri for the filename:

$url = "$($r.Name.Parts)"
 if ([system.uri]::IsWellFormedUriString($url, [system.urikind]::Absolute))
 {
 $u = ([uri]"$url").Segments[-1]
 $filename = "$d\$u.sql"
 new-item $filename -ItemType File -ErrorAction Stop -Force
 }

Example output

Directory: C:\temp\db

Mode LastWriteTime Length Name
—- ————- —— —-
d—– 3/5/2018 7:10 PM Assembly
d—– 3/6/2018 9:04 AM Contract
d—– 3/5/2018 7:18 PM DataType
d—– 3/5/2018 7:19 PM Endpoint
d—– 3/5/2018 7:19 PM Filegroup
d—– 3/6/2018 9:04 AM MessageType
d—– 3/5/2018 7:20 PM Queue
d—– 3/5/2018 7:20 PM Role
d—– 3/5/2018 7:20 PM Schema
d—– 3/6/2018 9:04 AM Service
d—– 3/5/2018 7:20 PM Table
d—– 3/5/2018 7:20 PM User
d—– 3/5/2018 7:20 PM UserDefinedType
-a—- 3/5/2018 7:06 PM 0 [Microsoft.SqlServer.Types].sql

Directory: C:\temp\db\Schema

Mode LastWriteTime Length Name
—- ————- —— —-
-a—- 3/6/2018 9:11 AM 54 dbo.sql
-a—- 3/6/2018 9:11 AM 76 db_accessadmin.sql
-a—- 3/6/2018 9:11 AM 82 db_backupoperator.sql
-a—- 3/6/2018 9:11 AM 74 db_datareader.sql
-a—- 3/6/2018 9:11 AM 74 db_datawriter.sql
-a—- 3/6/2018 9:11 AM 70 db_ddladmin.sql
-a—- 3/6/2018 9:11 AM 82 db_denydatareader.sql
-a—- 3/6/2018 9:11 AM 82 db_denydatawriter.sql
-a—- 3/6/2018 9:11 AM 64 db_owner.sql
-a—- 3/6/2018 9:11 AM 80 db_securityadmin.sql
-a—- 3/6/2018 9:11 AM 58 guest.sql
-a—- 3/6/2018 9:11 AM 84 INFORMATION_SCHEMA.sql
-a—- 3/6/2018 9:11 AM 54 sys.sql

The entire script is posted in a gist: