Create Sql DB with powershell searching for dacpac and naming db with Folder its located in

Question

I am trying to create databases in Sql Server by searching for *.dacpac files within a folder, For each .dacpac found I need to create a DB with the name of the Folder the .dacpac was found in.

Example

C:\Folder\ contains

C:\Folder\Project1\mydatabase.dacpac
C:\Folder\Project2\mydatabase.dacpac
C:\Folder\Project3\mydatabase.dacpac

Script to create Db

cd "C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin"
$createDB = .\sqlpackage.exe /Action:Publish /SourceFile:"c:\Folder\Project1\mydatabase.dacpac" /TargetDatabaseName:"Project1" /TargetServerName:"(LocalDB)\MSSQLLocalDB"

I can get the Folder names to use as TargetDatabaseName with the following

 PS C:\> $FolderName = Get-ChildItem C:\Folder | select -ExpandProperty Name
    $FolderName
    Project1
    Project2
    Project3

I am trying to use a foreach loop to find the dacpac files and use them as the SourceFile and then lookup the FolderName to use as TargetDatabaseName

$FolderName = Get-ChildItem C:\Folder | select -ExpandProperty Name
$dacpacfile = Get-ChildItem C:\Folder -Recurse -Include *.dacpac
    foreach ($dacpac in $dacpacfile){
        $createDB = .\sqlpackage.exe /Action:Publish /SourceFile:$dacpacfile /TargetDatabaseName:$FolderName /TargetServerName:"(LocalDB)\MSSQLLocalDB"}

The Error received is:

.\sqlpackage.exe : * Could not load package from 'C:\Folder\Project1\mydatabase.dacpac C:\Folder\Project2\mydatabase.dacpac C:\Folder\Project3\mydatabase.dacpac'. At line:4 char:21 + ... $createDB = .\sqlpackage.exe /Action:Publish /SourceFile:$dacpacfile ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (* Could not l...tabase.dacpac'.:String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError

I expect I need to create two arrays and try step through them together however not able to get this right, any help would be greatly appreciated.

komsky komsky · Accepted Answer · 2017-07-13T10:30:03

Here, try this:

$dacpacFiles = Get-ChildItem C:\project -Recurse -Include *.dacpac
cd "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin"
foreach ($dacpacFile in $dacpacFiles)
{
    $dir = Split-Path $dacpacFile.Directory -Leaf
    $filePath = $dacpacFile.FullName
    $createDB = .\sqlpackage.exe /Action:Publish /SourceFile:$filePath /TargetDatabaseName:$dir /TargetServerName:"."
}

Create Sql DB with powershell searching for dacpac and naming db with Folder its located in

1 Answers