用PowerShell脚本刷新Excel连接的SSIS？

Question

我正在构建一个SSIS包，用于将数据记录捕获到持久化SQL表中，用于分析用户会话。

下面是我试图创建的过程：

服务器上的Analysis [DISCOVER_SESSIONS]以 server -A为数据源。在“服务器-B”上，SSIS包[SSISDB/IsolatedPackages/SSASUsageStats.dtsx]写入自定义DB表[DBA].[dbo].[UsageStatsLogOLAP]。 使用Excel提取和转换源，并将其加载到Excel中，然后从Excel传输到Server表。SSIS执行Excel刷新和数据传输到Server。完成SSIS包后，Excel数据集将加载到Server中，以添加新记录。下面演示了Excel示例数据集。(注意: Excel由于其提取“CubeCommand”字段子字符串分析和从“SESSION_USER_NAME”中提取“ADUserNameDisplay”字段Active查找的简单性而被选择为数据提取ETL工具)。这使得分析用户OLAP会话超时成为可能。SSIS包将作为SQL代理作业每15分钟运行一次，以向自定义Server表添加新会话。

需要的帮助：

中是否有一种方法可以执行SSIS的PowerShell脚本来刷新PowerShell连接？另外，请记住，对Analysis实例的凭据需要对包进行身份验证吗？是否有更好的方法刷新Excel (而不是SSIS中的PowerShell )？

我研究过的一些更新选项..。

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

根据Task网站，Pro版本(每台服务器售价2,495美元)包括一个组件“”。有人对这个加载项有什么经验吗？我还没有尝试下载这个分机。

有关到目前为止使用的PoSH脚本，请参见下面的内容。这个包是功能性的，但还没有自动化。我需要关于Excel刷新自动化的帮助来完成这个包。我们很欣赏你的见解..。谢谢!

方法: Power脚本命令：

这个命令的问题是它打开了第二个power shell窗口。如果我在两个单独的脚本中运行此操作，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

参考文献：

• https://www.youtube.com/watch?v=gWJgYqYhjPo&index=9&list=PLBXFmpniYyOL8foPfqviOtzYEH2yE8jeP&t=0s
• Powershell Script - Open Excel, Update External Data, Save as

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

CozyRoc

在开发过程中，我们尝试使用自定义SSIS扩展(来自CozyRoc的“Excel”任务)来利用“重新计算”设置来刷新Excel工作簿。这并不像我所宣传的那样有效，当我联系CozyRoc支持人员时，他们也进行了验证，声明：“我们不支持外部数据连接的刷新”。

任务工厂

根据Task网站，Pro版本(每台服务器售价2,495美元)包括一个组件“”。有人对这个加载项有什么经验吗？我还没有尝试下载这个分机。

参考文献：

• https://www.capterra.com/integration-software/compare/146853-111875-170286-178934/API-Driver-vs-COZYROC-SSIS+-Library-vs-SSIS-PowerPack-vs-Task-Factory
• https://www.sentryone.com/products/task-factory/features?hsCtaTracking=dc730d58-98cf-4332-94ea-43a5a80a7c6c%7C694de72f-7002-4ace-97a9-e00d9c08ae0f
• https://www.cozyroc.com/ssis/excel-api

截图

ps-script_excel-刷新-2-windows.png

​

​

SSISDB-IsolatedPackages-SSASUsageStats.dtsx.png

​

​

exceldataset-DBA.dbo.UsageStatsLogOLAP.png

​

​

Answer 1

听起来是个痛苦的过程。如果是我，我会跳过SSIS包中的Excel + Powerquery位。只需将数据从DMV视图(带有时间戳)直接加载到表中即可。最好是Server 2016时态表。

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

然后，您可以将发送回时态查询的时间段参数化，以捕获不同的窗口--设置一个Excel列表"quanta“发送到时态查询，用户从该列表中选择一个值，然后从列表中触发该”量程“的数据刷新。

如果可以使用Server 2016+，请对时态表和查询进行一些研究。听起来他们会为你想做的事情提供便利。

在Excel (.xlsm)中，您可以让它在打开工作簿时刷新PowerQuery数据，工作簿应该始终使其成为“最新”数据。

Excel通常不是一个很好的工具，可以放在自动化数据分析过程的中间。它主要是一个用户端点工具.

如果您需要这样做的话，有大量的Powershell脚本可以“驱动”Excel (通过COM )。这使得从SSIS调用的Powershell进程负责在Powershell进程结束时撕毁COM对象。

在服务器上自动化Excel通过COM互操作可能会导致某些事情无法完全退出Excel，这将导致大量Excel僵尸进程占用资源。因此，这使得设置另一个进程定期杀死Excel僵尸进程.(网络上也有例子说明如何做到这一点)如果你不能完全清理Excel之后的sic。

如果您必须从服务器进程( SSIS应该是.)创建Excel文件，我过去就成功地使用了EPPlus .Net DLL和SSIS中的Powershell脚本。Powershell获取数据，然后调用EPPlus将数据直接写入到.xlsx文件(.xlsx) w/o，需要或与Excel.exe交互。(阅读作品也是)。

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

这两种方法的最大优点(我怀疑您注意到Powerquery对数据也没有.)您可以避开Excel将应用于加载到工作簿中的数据的“帮助”。(例如，使用EPPlus，您可以将邮政编码或US作为文本值推入单元格-您控制单元格样式和显示格式。Excel将“帮助”您将这些转换为数字数据，从而失去任何领先的0.试图克服这种反帮助涉及到许多不同的习俗)。