如何快速排查SQL Server 2008 R2中99个DTS包关联的500个数据库？

这确实是个需要批量处理的典型场景，咱们可以分三个核心步骤来搞定：批量解析文件系统中的DTS包、收集目标SQL Server的数据库列表、最后匹配汇总结果。下面是具体的操作细节：

1. 批量解析文件系统中的DTS包 #

DTS包分两种常见格式：XML-based的.dtsx（SQL Server 2008 R2常用的SSIS包）和旧版二进制.dts文件，咱们分别处理：

处理.dtsx包（XML格式） #

用PowerShell遍历所有包文件，解析XML提取连接字符串中的数据库名，以及SQL任务中直接引用的数据库：

# 设置DTS包所在的根目录，替换成你的实际路径
$dtsRootPath = "C:\YourDtsPackagesFolder"
$dbPackageMap = @{}

# 递归遍历所有.dtsx文件
Get-ChildItem -Path $dtsRootPath -Filter *.dtsx -Recurse | ForEach-Object {
    $packagePath = $_.FullName
    Write-Host "正在处理包: $packagePath"
    
    # 加载XML内容
    $xml = [xml](Get-Content $packagePath)
    
    # 提取连接管理器中的数据库名
    $xml.SelectNodes("//DTS:ConnectionManager", @{DTS="www.microsoft.com/SqlServer/Dts"}) | ForEach-Object {
        $connString = $_.Properties.Property | Where-Object { $_.Name -eq "ConnectionString" } | Select-Object -ExpandProperty InnerText
        if ($connString) {
            # 匹配连接字符串中的Initial Catalog（数据库名）
            $dbNameMatch = [regex]::Match($connString, "Initial Catalog=([^;]+)")
            if ($dbNameMatch.Success) {
                $dbName = $dbNameMatch.Groups[1].Value
                if (-not $dbPackageMap.ContainsKey($dbName)) {
                    $dbPackageMap[$dbName] = @()
                }
                if ($packagePath -notin $dbPackageMap[$dbName]) {
                    $dbPackageMap[$dbName] += $packagePath
                }
            }
        }
    }
    
    # 提取SQL任务中的数据库引用（三部分命名或USE语句）
    $xml.SelectNodes("//DTS:Executable[@DTS:ExecutableType='Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91']", @{DTS="www.microsoft.com/SqlServer/Dts"}) | ForEach-Object {
        $sqlCommand = $_.Properties.Property | Where-Object { $_.Name -eq "SqlStatementSource" } | Select-Object -ExpandProperty InnerText
        if ($sqlCommand) {
            $dbMatches = [regex]::Matches($sqlCommand, '\[?([^\]\.]+)\]?\.\[?[^\]\.]+\]?\.\[?[^\]\.]+\]?|\[?([^\]\.]+)\]?\.\.[^\s]+|USE\s+\[?([^\]\s]+)\]?', [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
            foreach ($match in $dbMatches) {
                $dbName = $match.Groups[1].Value
                if (-not $dbName) { $dbName = $match.Groups[2].Value }
                if (-not $dbName) { $dbName = $match.Groups[3].Value }
                if ($dbName) {
                    if (-not $dbPackageMap.ContainsKey($dbName)) {
                        $dbPackageMap[$dbName] = @()
                    }
                    if ($packagePath -notin $dbPackageMap[$dbName]) {
                        $dbPackageMap[$dbName] += $packagePath
                    }
                }
            }
        }
    }
}

# 导出映射结果到CSV
$dbPackageMap.GetEnumerator() | ForEach-Object {
    [PSCustomObject]@{
        DatabaseName = $_.Key
        关联的DTS包路径 = $_.Value -join ";"
        包数量 = $_.Value.Count
    }
} | Export-Csv -Path "D:\DTS_Package_Database_Map.csv" -NoTypeInformation -Encoding UTF8

处理旧版.dts二进制包 #

这类包需要用DTS COM对象来读取，同样用PowerShell实现：

$dtsRootPath = "C:\YourLegacyDtsPackagesFolder"
$dbPackageMap = @{}

Get-ChildItem -Path $dtsRootPath -Filter *.dts -Recurse | ForEach-Object {
    $packagePath = $_.FullName
    Write-Host "正在处理旧版DTS包: $packagePath"
    
    # 创建DTS包COM对象
    $dtsPackage = New-Object -ComObject DTS.Package
    try {
        $dtsPackage.LoadFromFile($packagePath, $null, $null)
        
        # 提取连接中的数据库名
        foreach ($conn in $dtsPackage.Connections) {
            if ($conn.ConnectionString) {
                $dbNameMatch = [regex]::Match($conn.ConnectionString, "Database=([^;]+)|Initial Catalog=([^;]+)")
                if ($dbNameMatch.Success) {
                    $dbName = $dbNameMatch.Groups[1].Value
                    if (-not $dbName) { $dbName = $dbNameMatch.Groups[2].Value }
                    if ($dbName) {
                        if (-not $dbPackageMap.ContainsKey($dbName)) {
                            $dbPackageMap[$dbName] = @()
                        }
                        if ($packagePath -notin $dbPackageMap[$dbName]) {
                            $dbPackageMap[$dbName] += $packagePath
                        }
                    }
                }
            }
        }
        
        # 提取SQL任务中的数据库引用
        foreach ($task in $dtsPackage.Tasks) {
            if ($task.CustomTask -is [ComObject] -and $task.CustomTask.GetType().Name -eq "ExecuteSQLTask") {
                $sqlCommand = $task.CustomTask.SQLStatement
                if ($sqlCommand) {
                    $dbMatches = [regex]::Matches($sqlCommand, '\[?([^\]\.]+)\]?\.\[?[^\]\.]+\]?\.\[?[^\]\.]+\]?|\[?([^\]\.]+)\]?\.\.[^\s]+|USE\s+\[?([^\]\s]+)\]?', [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
                    foreach ($match in $dbMatches) {
                        $dbName = $match.Groups[1].Value
                        if (-not $dbName) { $dbName = $match.Groups[2].Value }
                        if (-not $dbName) { $dbName = $match.Groups[3].Value }
                        if ($dbName) {
                            if (-not $dbPackageMap.ContainsKey($dbName)) {
                                $dbPackageMap[$dbName] = @()
                            }
                            if ($packagePath -notin $dbPackageMap[$dbName]) {
                                $dbPackageMap[$dbName] += $packagePath
                            }
                        }
                    }
                }
            }
        }
    } catch {
        Write-Warning "处理包 $packagePath 失败: $_"
    } finally {
        # 释放COM对象，避免内存泄漏
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($dtsPackage) | Out-Null
    }
}

# 导出结果
$dbPackageMap.GetEnumerator() | ForEach-Object {
    [PSCustomObject]@{
        DatabaseName = $_.Key
        关联的DTS包路径 = $_.Value -join ";"
        包数量 = $_.Value.Count
    }
} | Export-Csv -Path "D:\Legacy_DTS_Package_Database_Map.csv" -NoTypeInformation -Encoding UTF8

2. 收集SQL Server 2008 R2上的数据库列表 #

在目标SQL Server上运行以下查询，导出结果为CSV（比如SQL_Databases.csv）：

SELECT name AS DatabaseName
FROM sys.databases
WHERE state_desc = 'ONLINE' -- 只筛选在线数据库
ORDER BY name;

3. 匹配并汇总结果 #

用PowerShell将两个CSV文件合并，找出同时存在于DTS包映射和SQL数据库列表中的项：

# 读取之前导出的两个CSV文件
$dtsDbMap = Import-Csv -Path "D:\DTS_Package_Database_Map.csv"
$sqlDatabases = Import-Csv -Path "D:\SQL_Databases.csv"

# 如果有旧版包的映射，合并进来
if (Test-Path "D:\Legacy_DTS_Package_Database_Map.csv") {
    $legacyDtsMap = Import-Csv -Path "D:\Legacy_DTS_Package_Database_Map.csv"
    $dtsDbMap += $legacyDtsMap
}

# 找出被DTS包访问的本地数据库
$matchedDatabases = $dtsDbMap | Where-Object { $sqlDatabases.DatabaseName -contains $_.DatabaseName } | Group-Object DatabaseName | ForEach-Object {
    [PSCustomObject]@{
        DatabaseName = $_.Name
        关联的DTS包总数 = ($_.Group | Measure-Object -Sum 包数量).Sum
        关联的DTS包路径 = ($_.Group.关联的DTS包路径 -join ";") -replace ";;", ";"
    }
}

# 输出并导出最终结果
$matchedDatabases | Format-Table DatabaseName, 关联的DTS包总数, 关联的DTS包路径 -AutoSize
$matchedDatabases | Export-Csv -Path "D:\Matched_Databases.csv" -NoTypeInformation -Encoding UTF8

注意事项 #

• 如果DTS包中使用变量动态指定数据库名，上述脚本可能无法提取，需要手动检查这类包，或者扩展脚本解析变量的静态值（如果变量未在运行时动态生成）。
• 对于链接服务器上的数据库，脚本会误判为本地数据库，你可以在匹配时过滤掉非本地的数据库名（比如根据服务器名判断）。
• 运行PowerShell脚本时，确保有足够的权限读取文件系统中的DTS包，以及访问SQL Server数据库。

内容的提问来源于stack exchange，提问作者Brent Jenny