July 25, 2018 at 8:41 am
I'm trying to execute a Powershell script that sits on a network file share using SSIS Execute process task. When I run the package locally, it works fine, but when I run it on my server using SQL agent, the execute process task validates and then just sits there forever, never executing. I've verified that all the access is right. So I tried running just the first command from the script in my arguments instead of executing the file and it worked. Funny enough, the command involves moving files on a UNC path. So I'm at a loss. Additionally, if I store the file on the local server running the package, it also runs fine.
This hangs on the server only
Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: Powershell -ExecutionPolicy Unrestricted -File "\\fileshare\data\apps\BISharedServices\Powershell\pstest.ps1"
This runs fine
Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: Powershell -ExecutionPolicy Unrestricted -command "Move-Item -Path '\\fileshare\data\apps\BISharedServices\Files\*.json' -Destination '\\fileshare\data\apps\BISharedServices\Files\Archive' -Force"
This also runs fine
Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments: Powershell -ExecutionPolicy Unrestricted -command "D:\apps\Files\pstest.ps1"
July 25, 2018 at 12:36 pm
I suspect some type of permissions issue is causing the script to fail. You might have better luck using a Script Component and PowerShellInstance. This article outlines how to implement and use this: https://blogs.msdn.microsoft.com/kebab/2014/04/28/executing-powershell-scripts-from-c/
If your script is only moving/copying files - then I wouldn't even utilize powershell for that. I would do that in a script component directly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 25, 2018 at 2:01 pm
The script also downloads some json files from a webservice. That line was just the first step of archiving the objects before downloading new ones. I'll keep that in my back pocket, but the script and the files that being moved are in the same fileshare, so it seems like permissions shouldn't be an issue. And from that same server I do execute batch files on a UNC share using the same execute process tasks. There appears to just be something unique about executing powershell in this way.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply