— 1. PowerShell脚本
— 示例：导入销售数据
cat > /sqlserver/scripts/Import-SalesData.ps1 << 'EOF' #!/usr/bin/env pwsh # Import-SalesData.ps1 # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn param( [string]$SourcePath = "\\fgedu-prod-01\Data\Sales", [string]$Database = "fgedudb", [string]$Server = "fgedu-prod-01" ) # 日志文件 $LogFile = "/sqlserver/logs/Import-SalesData_$(Get-Date -Format 'yyyyMMdd').log" # 开始日志 "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - 开始导入销售数据" | Out-File -FilePath $LogFile -Append try { # 获取CSV文件 $Files = Get-ChildItem -Path $SourcePath -Filter "sales_*.csv" if ($Files.Count -eq 0) { "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - 没有找到CSV文件" | Out-File -FilePath $LogFile -Append exit 0 } # 处理每个CSV文件 foreach ($File in $Files) { "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - 处理文件: $($File.FullName)" | Out-File -FilePath $LogFile -Append # 导入数据 $Query = "BULK INSERT fgedu.sales FROM '$($File.FullName)' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)" Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $Query # 移动处理后的文件 $ArchivePath = "$SourcePath\Archive" if (!(Test-Path -Path $ArchivePath)) { New-Item -Path $ArchivePath -ItemType Directory -Force } Move-Item -Path $File.FullName -Destination "$ArchivePath\$($File.Name)_$(Get-Date -Format 'yyyyMMddHHmmss')" "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - 文件处理完成: $($File.FullName)" | Out-File -FilePath $LogFile -Append } # 结束日志 "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - 销售数据导入完成" | Out-File -FilePath $LogFile -Append } catch { "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - 错误: $($_.Exception.Message)" | Out-File -FilePath $LogFile -Append exit 1 } EOF -- 2. SQL脚本 -- 示例：数据处理 cat > /sqlserver/scripts/Process-SalesData.sql << 'EOF' -- Process-SalesData.sql -- from:www.itpux.com.qq113257174.wx:itpux-com -- web: http://www.fgedu.net.cn -- 处理销售数据 BEGIN TRY -- 开始事务 BEGIN TRANSACTION; -- 更新销售状态 UPDATE fgedu.sales SET status = 'COMPLETED' WHERE status = 'PENDING' AND sale_date < DATEADD(day, -1, GETDATE()); -- 计算销售总额 INSERT INTO fgedu.sales_daily_agg (sale_date, total_amount, total_quantity) SELECT CONVERT(DATE, sale_date) AS sale_date, SUM(amount) AS total_amount, SUM(quantity) AS total_quantity FROM fgedu.sales WHERE CONVERT(DATE, sale_date) = CONVERT(DATE, DATEADD(day, -1, GETDATE())) GROUP BY CONVERT(DATE, sale_date); -- 提交事务 COMMIT TRANSACTION; END TRY BEGIN CATCH -- 回滚事务 ROLLBACK TRANSACTION; -- 抛出错误 THROW; END CATCH; EOF -- 3. SSIS包 -- 示例：数据导出 -- 创建SSIS包：Export-SalesData.dtsx -- 包含以下组件： -- - 执行SQL任务：获取销售数据 -- - 数据流任务：导出数据到CSV文件 -- - 执行SQL任务：更新导出状态 -- 4. 存储过程 -- 示例：批处理存储过程 CREATE PROCEDURE fgedu.sp_batch_process_sales AS BEGIN SET NOCOUNT ON; -- 处理销售数据 BEGIN TRY -- 开始事务 BEGIN TRANSACTION; -- 更新销售状态 UPDATE fgedu.sales SET status = 'COMPLETED' WHERE status = 'PENDING' AND sale_date < DATEADD(day, -1, GETDATE()); -- 计算销售总额 INSERT INTO fgedu.sales_daily_agg (sale_date, total_amount, total_quantity) SELECT CONVERT(DATE, sale_date) AS sale_date, SUM(amount) AS total_amount, SUM(quantity) AS total_quantity FROM fgedu.sales WHERE CONVERT(DATE, sale_date) = CONVERT(DATE, DATEADD(day, -1, GETDATE())) GROUP BY CONVERT(DATE, sale_date); -- 提交事务 COMMIT TRANSACTION; END TRY BEGIN CATCH -- 回滚事务 ROLLBACK TRANSACTION; -- 抛出错误 THROW; END CATCH; END;

执行结果：