Powershell task completes successfully but doesn't actually work.

Question

Powershell task completes successfully but doesn't actually work.

steve.alston

Mr or Mrs. 500

Points: 525
More actions
January 17, 2023 at 5:51 am

#4138435

Hi
I hope someone can help me.
I've been running an SSIS script inVs 2015 for several months and it works perfectly. Unfortunately the demise of Windows 7 means we are moving to Windows 10. Everything is installed and the script builds and runs with the exception of a PowerShell Process Task.
I use a PowerShell Process Task to open an Excel workbook and run a macro which clears the contents of a couple of sheets in the workbook.
The process runs successfully with green ticks all the way. However the Process script doesn't clear the data from the sheets.
Running powershell.exe -F "C:\AVETMISS\Powershell\ResetSpreadsheet.ps1" from the command line works perfectly. And the process task works fine in Windows 7.
I did have to change the task success code from 0 in W7 to 1 in W10 to get it to run but that;s all I changed.
An I missing something stupid?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

I'm not sure I have ever 100% successfully ran a macro in a spreadsheet with any language. I have always created an empty .xls template, and then taken the existing data and moved, added, and modified that into the empty template, and then saved as, etc.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

steve.alston Mr or Mrs. 500 Points: 525 More actions · Answer 2

Thanks for the reply

Yes, it can be done. I have a feeling that the (more) locked down W10 work environment os the problem. I'm now fighting the security team.

The process task is: powershell.exe -F "C:\AVETMISS\Powershell\ResetSpreadsheet.ps1"

and the PS script is:

# lines starting with the # character are remarks or disabled in the script
# start Excel

$excel = New-Object -comobject Excel.Application

#open file
$FilePath = 'C:\AVETMISS\Returns Record working.xlsm' #<------- Change this!!!
$workbook = $excel.Workbooks.Open($FilePath)

#make it visible (just to check what is happening)
#$excel.Visible = $true

#access the Application object and run a macro
$app = $excel.Application
$app.Run("ResetSpreadsheet") #<------- Change this!!!

$excel.Quit()

#Popup box to show completion - you would remove this if using task scheduler
#$wshell = New-Object -ComObject Wscript.Shell $wshell.Popup("Operation Completed",0,"Done",0x1)

exit

Later in the SSIS script I use the same technique to append data from SQL server to the sheets I cleared here.

In our locked down environment I had to get permissions to run scripts in non standard folders and Powershell in full language mode.

In W7 it just comes with the correct permissions.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 3

it keeps surprising me that people still build stuff that is supposed to run on a server, were Excel/Office is neither licensed neither supported, and expect everything to work fine.

or that use their own workstations with SSDT to run production processes (again not licensed)

as for your powershell issue - Reading the manuals may be of help here - mainly the bit for the command line execution here. of interest to you is the "-executionpolicy" parameter