January 11, 2016 at 8:50 pm
Hello All -
I have an interesting situation. I will explain it below.
A vendor gave us a Python script which calls their API. This API call returns data to a CSV file. This script works fine and I compiled it to an .exe file so that I can run it on our server and this also runs just fine. I'm calling this .exe file via a Powershell script and when I run it manually it also works just fine.
The problem occurs when I try to execute this Powershell script from an Execute Process Task within an SSIS package. For some reason it doesn't see the invoke command and corresponding variables. The task turns green but there is no CSV file in the folder. Again, when I run this manually in Powershell it connects to the API and creates the CSV file just fine. I'm really confused about this.
I'm just wondering if there is something i'm missing in the Execute Process Task. I'm wondering also if there are permissions issues. Have any of you ever encountered this issue?
I'm not sure what you need to see but here is my very simple code from my Powershell script. Again, this runs JUST FINE when I run the file by itself outside of SSIS. I included the write-hose in my script because I wanted to see how far the code was going. It's get to the point of returning the date and "executing script..." that's it, which means that SSIS is not seeing the actual command to call the exe file. I also tried Invoke-Expression but that doesn't work in SSIS either (but it does work when I run it manually...).
Strange times in SSIS land, and further makes me dislike it even more than I already do 🙂
$app = 'C:\MyFolder\my.exe'
$date = Get-Date -Format "yyyy-MM-dd"
$fileName = "MyFile"
$convert = $date.ToString()
write-host 'executing script...'
& $app $date $fileName
write-host 'nanigans.exe called...'
Thanks!!
January 11, 2016 at 9:57 pm
Just an update, I tried removing the Powershell layer and just running the .exe file itself from SSIS, I get the following error:
[Execute Process Task] Error: In Executing "C:\MyFolder\my.exe" "2016-01-11 MyFile" at "", The process exit code was "255" while the expected was "0".
Is this due to some kind of permissions error? My folder has the same permissions as the other folders on the server, this is where our ETL processes are set up. I'm just not seeing anything different between them, and we have executables running in other SSIS packages.
What the hell could be causing this???
January 11, 2016 at 11:22 pm
Polymorphist (1/11/2016)
Just an update, I tried removing the Powershell layer and just running the .exe file itself from SSIS, I get the following error:
[Execute Process Task] Error: In Executing "C:\MyFolder\my.exe" "2016-01-11 MyFile" at "", The process exit code was "255" while the expected was "0".Is this due to some kind of permissions error? My folder has the same permissions as the other folders on the server, this is where our ETL processes are set up. I'm just not seeing anything different between them, and we have executables running in other SSIS packages.
What the hell could be causing this???
It could be anthing, there is not enough info to say at this point. The error generically means that the executable gave a return code of 255 when it completed. The return code is the only way in which the executable can communicate success or failure back to SSIS. SSIS interprets the return code based on the Execute Process Task properties FailTaskIfReturnCodeIsNotSuccessValue and SuccessValue.
You can also capture stdout and stderr into variables. Did you do that? What did they say when your executable returned 255?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 12, 2016 at 7:45 pm
I didn't have a chance to get back to this until this evening. I've figured out the problem, and as I suspected, it was an easy one. In the Execute Process Task, I had to point the Working Directory variable to the folder where the .exe file is located.
I use SSIS infrequently enough that i'll probably forget, but there it is 🙂
January 12, 2016 at 8:53 pm
That does not compute...when an Execute Process Task cannot find the executable at all you should see an error like:
Error: 0xC0029154 at Execute Process Task, Execute Process Task: File/Process "C:\MyFolder\Some.exe" is not in path.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply