Following the last blog post – How to Export BizTalk Applications resources to an XML file with PowerShell – and because that previous approach didn’t work for me, because, either failed to export, gave a timeout, or simply got stuck trying to accomplish this resource exportation. I then decide to use a different strategy.
📝 One-Minute Brief
This is a PowerShell script that goes to the BizTalkMgmtDb database in order to export a list of all assemblies of a specific BizTalk application and copies the DLL from the GAC to a local folder.
But this time, do it in style! The goal was:
- Based on the application name, access the internal databases of BizTalk Server and get a list of all resources belonging to that application
- Then, based on the list provided from BizTalk Server databases, go to the GAC and get a copy of the DLLs into a folder
Here is a sample of the PowerShell script:
# Run the SQL (returns XML) using .NET client
$cn = New-Object System.Data.SqlClient.SqlConnection
$cn.ConnectionString = "Server=$SqlServer;Database=$DbName;Trusted_Connection=True;MultipleActiveResultSets=False;"
$cn.Open()
# Your SQL (parametrized). It returns XML with <Assemblies><Assemby><nvcName>...</nvcName>...</Assemblies>
$q = @"
SELECT [nvcName]
FROM [dbo].[bts_assembly]
WHERE nApplicationID LIKE (
SELECT [nID]
FROM [dbo].[bts_application]
WHERE nvcName LIKE @app
)
FOR XML PATH('Assemby'), ROOT('Assemblies');
"@
$cmd = $cn.CreateCommand()
$cmd.CommandText = $q
$null = $cmd.Parameters.Add("@app",[System.Data.SqlDbType]::NVarChar,256)
$cmd.Parameters["@app"].Value = $AppName
# Use ExecuteXmlReader to get the XML cleanly
$xmlReader = $cmd.ExecuteXmlReader()
# Load into [xml]
$xml = New-Object System.Xml.XmlDocument
$xml.Load($xmlReader)
$xmlReader.Close()
$cn.Close()
# Extract assembly simple names (from <Assemby><nvcName>...)
$asmNames = @()
$nodes = $xml.SelectNodes("//Assemblies/Assemby/nvcName")
foreach ($n in $nodes) {
$val = [string]$n.InnerText
if ([string]::IsNullOrWhiteSpace($val)) { continue }
# In BizTalk, nvcName is the simple assembly name (no version/PKT). Keep as-is.
$asmNames += $val.Trim()
}
if (-not $asmNames -or $asmNames.Count -eq 0) {
Write-Warning "No assemblies found in SQL for application LIKE '$AppName'."
exit 1
}
# Locate each DLL in the GAC and copy it
$gacRoot = Join-Path $env:WINDIR 'Microsoft.NET\assembly'
$gacBuckets = @('GAC_MSIL','GAC_32','GAC_64')
$report = @()
$errors = @()
foreach ($name in $asmNames | Select-Object -Unique) {
$dllPath = $null
foreach ($bucket in $gacBuckets) {
$nameDir = Join-Path (Join-Path $gacRoot $bucket) $name
if (-not (Test-Path -LiteralPath $nameDir)) { continue }
# Search for <Name>.dll under the assembly folder; we don't know version/PKT from this query
$hits = @(Get-ChildItem -LiteralPath $nameDir -Recurse -Filter ("{0}.dll" -f $name) -ErrorAction SilentlyContinue)
if ($hits.Count -gt 0) {
# If multiple versions exist, pick the newest folder by LastWriteTime
$dllPath = ($hits | Sort-Object LastWriteTime -Descending | Select-Object -First 1).FullName
break
}
}
if (-not $dllPath) {
$msg = "GAC path not found for $name."
Write-Warning $msg
$errors += $msg
continue
}
try {
$dest = Join-Path $DestinationFolder ("{0}.dll" -f $name)
Copy-Item -LiteralPath $dllPath -Destination $dest -Force:$Overwrite
$report += [pscustomobject]@{
AssemblyName = $name
SourceGacPath = $dllPath
CopiedTo = $dest
Result = 'Copied'
}
Write-Host "Copied: $name -> $dest" -ForegroundColor Green
}
catch {
$msg = "Failed to copy $name from '$dllPath' -> '$DestinationFolder'. Error: $($_.Exception.Message)"
Write-Warning $msg
$errors += $msg
$report += [pscustomobject]@{
AssemblyName = $name
SourceGacPath = $dllPath
CopiedTo = $null
Result = 'Error'
}
}
}
And it will work… at least 90%!
The problem is that I cannot, at least yet, find a way to find the assembly’s helper classes. For example, the application below has two BizTalk Assemblies and a .NET assembly. The script can get the BizTalk Server Assemblies, but it cannot find them in the database, the .NET assembly, which, for me, is a no-go.
Nevertheless, this is a good resource.
Download
THIS POWERSHELL SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.
You can download the PowerShell Script used from GitHub here:
Hope you find this helpful! If you enjoyed the content or found it useful, and wish to support our efforts to create more, you can contribute to purchasing a Star Wars Lego set for my son!
