December 15, 2020 at 3:48 am
Hi Guys
A weird one here.
I run a process task to run powershell.exe with the argument -File C:\Ace\ResetSpreadsheet.ps1
This script runs a macro in a spreadsheet in the same folder to clear existing data and then saves and exits.
Because the IT people lock our boxes down to the nth degree I had issues with
macros being prohibited in the Ace folder , now allowed, and
Powershell not running in fullLanguage mode, now fixed.
The script clears the spreadsheet when run in the PowerShell ISE and running powershell.exe -File C:\Ace\ResetSpreadsheet.ps1 from the 'DOS' prompt also clears the spreadsheet.
But when I run the process in SSIS it fails returning a process exit code of 1 instead of the expected 0 and the data remains in the spreadsheet.
I'm assuming there's another Windows 10 security lock that I need to get removed because the task has worked fine in Windows 7 for years.
Does anyone have any ideas what/where it could be?
December 15, 2020 at 2:34 pm
My first question would be why are you running that from SSIS? That feels like overkill when windows task scheduler can handle it perfectly well. And failing that, SQL Server Agent can handle running powershell as well.
BUT assuming that running it inside SSIS is a requirement, my guess is that the user who the SSIS package is running as doesn't have the appropriate permissions. I'd look into capturing the errors from powershell and reviewing them after the package runs. This will give you a better idea as to why it failed, but my guess is that the SSIS Service account doesn't have permissions to the file, the folder, or to something being run.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply