Powershell和bcp工具实现带多组参数和标签的SQL Server数据库批量数据导出程序

设计一个基于多个带标签SQL模板作为配置文件和多组参数的Powershell代码程序和bcp工具，实现根据不同的输入参数，自动批量地将SQL Server数据库的数据导出为CSV文件到指定目录上，标签和多个参数（以“_”分割）为组成导出数据文件名，文件已经存在则覆盖原始文件。Powershell程序需要异常处理，输出带时间戳和每个运行批次和每个导出文件作业运行状态的日志文件，每天单独一个带日期的和.log扩展名日志文件，放在logs子目录中，参数全部设置在json配置文件中。

<#
.SYNOPSIS
SQL数据批量导出工具（基于bcp实用程序）.DESCRIPTION
根据JSON配置自动生成CSV文件，支持多模板多参数组合，带完整日志和异常处理
#># 初始化配置
$configPath = "config.json"
$logDir = ".\logs"
$currentDate = Get-Date -Format "yyyyMMdd"
$logFile = Join-Path $logDir "$currentDate.log"# 创建日志目录
if (-not (Test-Path $logDir)) { New-Item -ItemType Directory -Path $logDir | Out-Null }# 日志记录函数
function Write-Log {param([string]$Message,[ValidateSet("INFO","ERROR","WARNING")][string]$Level = "INFO")$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"$logEntry = "[$timestamp] [$Level] $Message"Add-Content -Path $logFile -Value $logEntry
}# 异常处理函数
function Handle-Error {param([string]$Message,[string]$TemplateLabel,[string]$Parameters)Write-Log -Message "导出失败 [$TemplateLabel] 参数: $Parameters - $Message" -Level ERROR
}# 主处理函数
function Invoke-SqlExport {try {# 加载配置文件$config = Get-Content $configPath | ConvertFrom-Json$connectionString = "Server=$($config.Database.Server);Database=$($config.Database.DatabaseName);Trusted_Connection=$($config.Database.IntegratedSecurity);"Write-Log "========== 导出作业启动 =========="Write-Log "数据库服务器: $($config.Database.Server)"Write-Log "目标目录: $($config.ExportSettings.OutputDirectory)"# 遍历所有SQL模板foreach ($template in $config.SqlTemplates) {Write-Log "处理模板 [$($template.Label)]"# 读取SQL模板文件$sqlTemplate = Get-Content $template.TemplatePath -Raw# 处理每个参数组foreach ($paramGroup in $template.ParameterGroups) {$currentSQL = $sqlTemplate$fileNameParams = @()try {# 替换SQL参数foreach ($param in $paramGroup.Parameters.PSObject.Properties) {$currentSQL = $currentSQL -replace "\{\{$($param.Name)\}\}", $param.Value$fileNameParams += $param.Value}# 生成文件名$fileName = $template.Label + "_" + ($fileNameParams -join "_") + ".csv"$outputPath = Join-Path $config.ExportSettings.OutputDirectory $fileName# 构建bcp命令$bcpCommand = "bcp ""$currentSQL"" queryout ""$outputPath"" " +"-c -t$($config.ExportSettings.CsvDelimiter) " +"-S $($config.Database.Server) " +"-d $($config.Database.DatabaseName) "if ($config.Database.IntegratedSecurity -eq "True") {$bcpCommand += "-T"} else {$bcpCommand += "-U $($config.Database.Username) -P $($config.Database.Password)"}# 执行导出Write-Log "正在导出: $fileName"$result = Invoke-Expression $bcpCommand 2>&1if ($LASTEXITCODE -ne 0) {throw $result}Write-Log "成功导出: $fileName (文件大小: $(Get-Item $outputPath | Select-Object -ExpandProperty Length) bytes)"}catch {Handle-Error -Message $_.Exception.Message -TemplateLabel $template.Label -Parameters ($fileNameParams -join "_")}}}}catch {Write-Log "全局异常: $($_.Exception.Message)" -Level ERROR}finally {Write-Log "========== 导出作业完成 =========="}
}# 启动导出任务
Invoke-SqlExport

配置文件示例 (config.json):

{"Database": {"Server": "localhost","DatabaseName": "AdventureWorks","IntegratedSecurity": "True","Username": "","Password": ""},"ExportSettings": {"OutputDirectory": "D:\\DataExports","CsvDelimiter": ",","Encoding": "UTF8"},"SqlTemplates": [{"Label": "SalesData","TemplatePath": "templates\\sales_query.sql","ParameterGroups": [{"Parameters": {"Year": "2023","Region": "North"}},{"Parameters": {"Year": "2024","Region": "South"}}]},{"Label": "Inventory","TemplatePath": "templates\\inventory.sql","ParameterGroups": [{"Parameters": {"Category": "Electronics"}}]}]
}

SQL模板文件示例 (sales_query.sql):

sql">SELECT * FROM Sales.Orders 
WHERE OrderYear = {{Year}} 
AND RegionCode = '{{Region}}'

系统结构说明：

配置文件架构：
- Database：数据库连接配置
- ExportSettings：导出设置
- SqlTemplates：模板数组，每个包含：
  - Label：文件前缀标签
  - TemplatePath：SQL模板文件路径
  - ParameterGroups：多个参数组配置
文件命名规则：
- {标签}{参数1}{参数2}.csv
- 示例：SalesData_2023_North.csv
日志系统特性：
- 每日独立日志文件（YYYYMMDD.log）
- 日志包含：
  - 时间戳（毫秒级）
  - 操作类型（INFO/ERROR）
  - 详细状态信息
  - 错误堆栈跟踪
异常处理机制：
- 模板文件缺失处理
- 数据库连接失败处理
- BCP执行错误捕获
- 文件系统权限错误处理
增强功能：
- 自动创建缺失目录
- 文件覆盖处理
- 导出文件大小验证
- BCP返回代码检查