PowerShell刷新Excel而不打开excel文件(SQL Agent打包计划的执行问题) [英] PowerShell Refresh Excel without opening excel file (Package scheduled execution issue with SQL Agent)

问题描述

我有一个开发的SSIS程序包，其中包含执行过程任务"和数据流任务".我在执行程序包时遇到问题.

I have an SSIS Package I have developed with an 'Execute Process Task' and 'Data Flow Task'. I am having an issue with execution of the package.

结果:(手动还是计划)

RESULTS: (Manual vs. Scheduled)

当我在 VS 和 SSISDB 中以32位模式手动执行时，所有进程均成功使用特定的用户个人资料"GEORGES/BL0040EP"登录.

All processes are successful when run executed manually in 32-bit mode from VS and SSISDB when I login with a specific user profile 'GEORGES/BL0040EP'.

1. 执行流程任务"-运行powershell脚本以刷新excel连接并保存excel文件. (成功)
2. 数据流任务"-读取Excel数据并将其插入到SQL Server表中. (成功)

1. 'Execute Process Task' -- Run a powershell script to refresh excel connections and save the excel file. (SUCCESS)
2. 'Data Flow Task' -- Reads the excel data and inserts it to SQL Server table. (SUCCESS)

我正在尝试使用 SQL Agent (使用代理帐户)运行该程序包，该过程存在一些问题.

I am trying to run the package with SQL Agent (using a proxy account), and the process has some issues.

1. 执行流程任务"-运行powershell脚本刷新excel连接(不起作用，没有错误消息).保存Excel文件(SUCCESS).
2. 数据流任务"-读取Excel数据并将其插入到SQL Server表中. (成功)

1. 'Execute Process Task' -- Run a powershell script to refresh excel connections (DOES NOT WORK, NO ERROR MESSAGES). Save the excel file (SUCCESS).
2. 'Data Flow Task' -- Reads the excel data and inserts it to SQL Server table. (SUCCESS)

问题:

从SQL Agent运行时，powershell script to refresh the Excel file似乎没有发布到SSAS服务器.不是成功查询问题".不是权限错误的查询问题".只是根本不执行查询".不会记录或检测到任何权限问题.

The powershell script to refresh the Excel file seemingly does not get issued to the SSAS Server when run from SQL Agent. Not "query issues successfully". Not "query issues with permissions error". Simply "query is not at all executed". No permissions issues are logged or detected.

我知道了，因为我在服务器上运行了SQL Profiler.当Agent调用程序包时，没有查询活动.当VS/SSISDIB调用该程序包时，我可以看到正在发出查询.都成功使用了用户配置文件(GEORGES \ bl0040ep)；并且未授权用户配置文件(GEORGES \ bl0040)出现权限错误，但未成功.

I can tell because I ran SQL Profiler on the server. When Agent calls the package there is no query activity. When VS/SSISDIB calls the package I can see the query being issued. Both successfully with user profile (GEORGES\bl0040ep); and unsuccessfully with permissions error with an unauthorized user profile (GEORGES\bl0040).

问题:

为什么SQL Agent不运行查询?

我什至在posh命令$env:UserName | Out-File -filepath中添加了输出包含用户名的文本文件的功能.并且代理帐户设置似乎正在预期的用户配置文件上下文下运行.文本文件的内容为bl0040ep.

I even added to the posh command $env:UserName | Out-File -filepath to output a text file containing the user name. And the Proxy Account setup appears to be running under the expected user profile context. Content of the text file is bl0040ep.

创建代理帐户

创建代理用户以在SQL Server代理中运行SSIS包

USE master 
GO

-- Create a proxy credential for xp_cmdshell.
EXEC sp_xp_cmdshell_proxy_account 'GEORGES\bl0040ep', '!myPW!';--SELECT  * FROM [master].[sys].[credentials]

-- Grant execute permission on xp_cmdshell to the SQL Server login account. 
GRANT exec ON sys.xp_cmdshell TO [GEORGES\bl0040ep] 
GO

-- Create a credential containing the GEORGES account PowerGEORGES\PowerUser and its password
CREATE CREDENTIAL Credential_BL0040EP WITH IDENTITY = N'GEORGES\bl0040ep', SECRET = N'!myPW!'
GO

USE [msdb]
GO
-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'Proxy_BL0040EP',@credential_name=N'Credential_BL0040EP',@enabled=1

-- Grant SSISProxy access to the "SSIS package execution" subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Proxy_BL0040EP', @subsystem_id=11

-- Grant the login testUser the permissions to use SSISProxy
EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'GEORGES\bl0040ep', @proxy_name=N'Proxy_BL0040EP'
GO

数据库SSAS_UsageStats_xlsx_ExcelRefresh.ps1

# Refresh the excel workbook connections and save the updated file
$file = 'C:\SVN\BusinessAnalysts\ExcelTools\DatabaseSSAS_UsageStats.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'
$filename = 'C:\SVN\BusinessAnalysts\ExcelTools\DatabaseSSAS_UsageStats.xlsx' 
$env:UserName | Out-File -filepath C:\SVN\BusinessAnalysts\ExcelTools\RefreshAll_process.txt
$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

SQL事件探查器

从SQL Agent运行程序包时未捕获任何活动.从VS和SSISDB运行时，捕获到登录错误.

No activity is captured when running the package from SQL Agent. When run from VS and SSISDB, a login error is captured.

SQLProfiler_(VS/SSIDB-connectionerror_user-bl0040).png

SQLProfiler_(SQLAGENT-connection_no-activity).png

没有图像可附加.当在SQL Agent上下文中运行程序包时，根本就没有用户活动(bl0040ep).

There is no image to attache. Simply there is no activity for the user (bl0040ep) when the package is run under the SQL Agent context.

星期三02/06/2019 14:31:46.96

Wed 02/06/2019 14:31:46.96

更新1:系统桌面文件夹

我在System32上添加了桌面"文件夹，从SQL Agent运行了作业，问题仍然存在.在TechNet 通过SQL Server代理执行的简单脚本的问题 ... C:\Windows\System32\config\systemprofile\Desktop和C:\Windows\SysWOW64\config\systemprofile\Desktop.该文件夹已经存在于SysWOW64上.

I added ‘Desktop’ folder on System32, ran the job from SQL Agent, and the issues persists. This was recommended on a similar issue reported on TechNet Issues with simple script executed via SQL Server Agent... C:\Windows\System32\config\systemprofile\Desktop and C:\Windows\SysWOW64\config\systemprofile\Desktop. The folder already existed on SysWOW64.

更新2:32位PowerShell可执行文件

我还试图直接调用32位版本的PowerShell:%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe.通过带有代理帐户的SQL Agent运行时，excel刷新仍未完成.参考在Sql Server代理上运行32位Powershell脚本

I have also tried to directly invoke the 32-bit version of PowerShell: %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe. Still the excel refresh does not complete when run via SQL Agent with proxy account. Reference Run a 32 bit Powershell script on Sql Server Agent

更新3:Windows任务计划程序，而不是SQL代理作业

我已经尝试了用于包执行的另一种调度方法(Windows Task Scheduler，而不是SQL Agent).计划已完成，但其行为与SQL Agent完全相同...查询未发送到数据源.参考 Nirav的日记，无需部署即可安排SSIS包，2-Windows计划任务

I have tried a different scheduling method for the package execution (Windows Task Scheduler, instead of SQL Agent). The schedule completes but it behaves the exact same way that SQL Agent does... the query is not being sent to the datasource. Reference Nirav's Diary, Schedule SSIS Package Without Deploying, 2-Windows Schedule Task

推荐答案

我已通过从图片中删除Excel来解决此问题.我现在使用的是连接器.Net Providers for OleDb\Microsoft OLE DB Provider for Analysis Services 2.0，而不是Excel源.数据库中还需要执行几个额外的步骤来获取相同的数据集(使用SQL Views而不是Excel Powerquery M).

I have solve this by removing Excel from the picture. Instead of Excel source I am now using the connector .Net Providers for OleDb\Microsoft OLE DB Provider for Analysis Services 2.0. There are a couple extra steps needed in the database to get the same dataset (using SQL Views instead of the Excel Powerquery M).

我将这场战斗输给了Excel ...它不想被自动化.最后，我确实仍然使用相同的CREDENTIALS和PROXY来安排工作.

I lost this fight to Excel... it did not want to be automated. I did end up still using the same CREDENTIALS and PROXY to schedule the job.

这篇关于PowerShell刷新Excel而不打开excel文件(SQL Agent打包计划的执行问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！