[SOLVED] SQL Package extract command is failing in PowerShell script

SQL Package extract command is failing in PowerShell script

I wrote a PowerShell script that uses SQL Package to create a dacpac and apply it to my local db. This script worked fine and I was getting ready to share it and it started throwing an error.

An unexpected failure occurred: The type initializer for 'Microsoft.SqlServer.Dac.DacServices' threw an exception..

Unhandled Exception: System.TypeInitializationException: The type initializer for 'Microsoft.SqlServer.Dac.DacServices' threw an exception. ---> System.TypeInitializationException: The type initializer for 'SqlSchemaModelStaticState' threw an exception. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlCoreAnnotationRegister.RegisterModelAnnotations(ModelSchema storeSchema)
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState.RegisterModelSchema()
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.SqlSchemaModelStaticState.get_ModelSchema()
   at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel.InitializeModelSchema()
   at Microsoft.SqlServer.Dac.DacServices..cctor()
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Dac.DacServices..ctor(String connectionString)
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoExtractDacpacOperation(CommandLineArguments parsedArgs)
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandLineArguments parsedArgs)
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args)
   at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args)

Nothing in my environment was changed. The possible exception is I installed VS 2022 (community), with SSDT. I just don't remember if it was between it running and not running. However, I am running this script in PS on my local machine, not connected to any VS projects.

I've tried uninstalling VS. I also reinstalled SQL Package.

Here is the script. It throws the error when it calls the SQL Package at sqlpackage $arguments

$tableData = ""
$dacpacFile = "databasename.dacpac"
$outputPath = "./"
$config = Get-Content "$PsScriptRoot\DacpacToLocalDb.json" | ConvertFrom-Json
$sourceServer = $config.SourceServer
$sourceDatabase = $config.SourceDatabase
$targetServer = $config.TargetServer
$targetDatabase = $config.TargetDatabase

if($(Write-Host "Copy Data Also? Y / N:  " -NoNewline -ForegroundColor Green; Read-Host) -eq 'y')
{ 
    $tableData = "/p:ExtractAllTableData=True"
}

Write-Host "Generating Dacpac file..." -ForegroundColor Green
$sourceConnectionString = "Server=$sourceServer;Database=$sourceDatabase;Encrypt=False; Integrated Security=SSPI;"
$arguments = "/Action:""Extract""", "/SourceConnectionString:""$sourceConnectionString""", "/TargetFile:""./$dacpacFile""", $tableData
sqlpackage $arguments

if($(Write-Host "Generate script? Y / N:  " -NoNewline -ForegroundColor Green; Read-Host) -eq 'y')
{
    Write-Host "Creating the SQL script..." -ForegroundColor Green
    $script = "$bin\$([System.IO.Path]::GetFileNameWithoutExtension($dacpacFile)).sql"
        $targetConnectionString = "Server=$targetServer;Database=$targetDatabase;Encrypt=False; Integrated Security=SSPI;"
        $arguments = "/Action:Script", "/SourceFile:""$dacpacFile""", "/TargetConnectionString:""$targetConnectionString""", "/OutputPath:""./dacpacScript.sql"""
        $arguments+=$sqlCmdVariables
        & sqlpackage $arguments
}

if($(Write-Host "Publish to '$targetServer'? Y / N:  " -NoNewline -ForegroundColor Green; Read-Host) -eq 'y')
{
    Write-Host "Applying file to target db..." -ForegroundColor Green
    $targetConnectionString = "Server=$targetServer;Database=$targetDatabase;Encrypt=False; Integrated Security=SSPI;"
    $arguments = "/Action:Publish", "/SourceFile:""./$dacpacFile""", "/TargetConnectionString:""$targetConnectionString""", "/p:ExcludeObjectTypes=Logins;ServerRoleMembership;ServerRoles;Credentials;Rolemembership;DatabaseRoles;Users", "/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor", `
    "/p:AdditionalDeploymentContributorPaths= ./", "/p:AdditionalDeploymentContributorArguments=SqlPackageFilter=IgnoreSchema(svc.solarwinds)"
    & sqlpackage $arguments
}

Solution

While both comments were invaluable, I think @IVNSTN's comment was closest to the case. I set this problem aside for a couple days to work on getting an API project running in my local environment. Since I had uninstalled VS during my troubleshooting, I had to reinstall it for the API project. (VS is not my team's IDE). After that was done I went back to my script and it was working as expected. I'm not sure what it fixed but I'll take it.