代码之家 › 专栏 › 技术社区 › SherlockSpreadsheets

SSIS是否使用PowerShell脚本刷新Excel连接?

permissions ssis powershell excel sql-server

SherlockSpreadsheets · 技术社区 · 6 年前

以下是我尝试创建的过程:

分析服务DMV [DISCOVER_SESSIONS] ' '用作数据源。在' “SSIS 包裹 [SSISDB/IsolatedPackages/SSASUsageStats.dtsx] 自定义数据库表 [DBA].[dbo].[UsageStatsLogOLAP] .

源代码用excelpowerquery进行提取和转换,加载到Excel,并从Excel传输到SQL Server表。SSIS公司执行Excel刷新并将数据传输到SQL Server。SSIS包完成后,将生成Excel数据集加载到SQL Server以添加新记录。Excel示例数据集如下所示。 提取“CubeCommand”字段的简单性[子字符串分析], 以及“ADUserNameDisplay”字段[从 .

这允许分析用户OLAP会话。SSIS 包将作为SQL代理作业每15分钟运行一次以添加新的

要验证包所需的Analysis Services实例吗?是 SSIS)?

我研究了一些刷新选项。。。

 SSIS PowerShell script
 SSIS VB script
 SSIS C# script
 3-party SSIS software (CozyRoc, PowerPack, TaskFactory)

请看下面的豪华脚本使用到目前为止。该软件包功能正常,但尚未实现自动化。我需要帮助有关的Excel刷新自动完成包。感谢您的真知灼见。。。谢谢!

方法:powershell脚本命令:

这个命令的问题是它会打开第二个powershell窗口。如果我在两个单独的脚本中运行,Excel工作簿将成功更新。如果将此作为单个脚本运行,它将使用我的用户凭据(而不是提供的用户凭据)--Excel文件保存成功,但数据刷新不成功。

##### STEP 1 ######
# Run powershell as another user account (DOMAIN\?????), for accessing the GCOP039 OLAP DMV 
cd C:
# REM: Define domain username and password 
$username = 'DOMAIN\bl0040ep'
$password = '!mySecretPwd'
# REM: Convert to a single set of credentials
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential $username, $securePassword
# REM: Launch PowerShell (runas) as another user
Start-Process powershell.exe -Credential $credential #-WindowStyle Maximized


##### STEP 2 ######
# Refresh the excel workbook connections and save the updated file
$file = 'C:\SVN\BusinessAnalysts\ExcelTools\DatabaseSSAS_Usage Stats.xlsx'
$x1 = New-Object -ComObject Excel.Application
$x1.Visible = $false
$x1.DisplayAlerts = $False
$enddate = (Get-Date).tostring("dd-MM-yy")
$filename = 'C:\SVN\BusinessAnalysts\ExcelTools\DatabaseSSAS_Usage Stats ' + $enddate + '.xlsx'
$wb = $x1.workbooks.Open($file)
$wb.refreshall()
# REM: Use SLEEP to eliminate the message: "This will cancel a pending data refresh. Continue?"
Start-Sleep -Second 20
$wb.SaveAs($filename)
$wb.Close()
$x1.Quit()
Remove-Variable wb,x1

参考文献:

方法:自定义SSIS扩展(CozyRoc、PowerPack、TaskFactory):

舒适的

在开发过程中,我们尝试使用自定义SSIS扩展(来自CozyRoc的Excel源加任务)来利用重新计算设置来刷新Excel工作簿。当我联系CozyRoc的支持人员时,他们也证实了这一点:我们不支持刷新外部数据连接。

任务工厂

参考文献:

截图

SSISDB-IsolatedPackages-SSASUsageStats.dtsx.png

exceldataset-DBA.dbo.usagestastslogolap.png

0 回复 | 直到 6 年前

user1390375 6 年前

然后,在SSIS进程之外为最终用户设置一个Excel电子表格,从时态表查询中提取数据。由于时态表查询的性质,它们上的默认段/窗口总是“最新的”(不管它是为时态表定义的……)。

如果您可以使用SQL Server 2016+,请研究时态表和查询。听起来他们会帮你做你想做的事。

Excel通常不是一个很好的工具,用于自动数据分析过程。它主要是一个用户终端工具。

如果需要这样做,有很多Powershell脚本可以“驱动”Excel(通过COM Interop)。这使得从SSIS调用的Powershell进程在Powershell进程结束时负责拆除COM对象。

在服务器上通过COM互操作自动化Excel有可能导致无法完全退出Excel,这将导致大量Excel僵尸进程占用资源。这样就剩下设置另一个进程周期性地杀死一个Excel僵尸进程(如果你不能在Excel之后完全清除[sic],网络上也有一些例子说明了如何做到这一点。

如果您必须从服务器进程(SSIS将是…)创建Excel文件,我过去在SSIS中使用带有Powershell脚本的EPPlus.Net DLL取得了成功。Powershell获取数据,然后调用EPPlus将数据直接写入Excel文件(.xlsx),不需要Excel.exe或与之交互(读书也有用)。

“NPOI.dll”库是Java“POI”库的.Net端口,它还可以写入“Excel.Old”(.xls)文件以及.xlsx文件。它的工作原理与EPPlus类似。