Execute Process Tasks Work Individually but Not When Running the Package
-
I have a weird situation here -
I have two Execute Process Tasks in my SSIS package, one of which retreives a file from AWS S3 server and another which unzips the .gz file after the file is downloaded. Both of these tasks work when I run them alone, however when I run them at the package-level the task which unzips the .gz file fails.
Anyone know why it would do this?
Doesn't make sense to me that there would be a permissions issue - when I run the tasks individually as a test isn't the same account being used as when I execute the package??
Very confusing and frustrating, I must say. Most people I know won't use SSIS for reasons such as this, just a hassle of a tool in my opinion. The company where i'm currently stationed uses it, so i'm stuck with SSIS for now.
In any case, i'm running SSIS under BIDS in SQL Server 2008 R2 and using 7zip to unzip the .gz files.
Thanks!!
-
LMGTFY.......
It's permissions.
Steve.
-
Thanks for the reply, Steve. Just so you know, I did Google and search for an answer but didn't find anything which helped which is why I ended up here.
I think what might be happening is that my second task is executing before the file actually hits the folder. I'm going to try and test this today and post the result.
-
you got me on a bad day What is the error you're getting when you try to run the package versus the steps individually?
To prove out your theory can you add a false wait (a loop or script) and see whether that makes a difference?
Steve.
-
The actual error i'm getting is below:
Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:\Program Files\7-Zip\7z.exe" "e C:\EDWDataExtracts\AWS_S3\abtest_0*.txt.gz" at "C:\EDWDataExtracts\AWS_S3", The process exit code was "2" while the expected was "0"
For some reason i'm getting the feeling that the file is somehow locked after it lands in the AWS_S3 folder. If I run the task to unzip the file by itself, it extracts properly and without any errors. I did try putting a Script Task between the two Process Tasks and had it sleep for two minutes but I got the same error. Some kind of locking is the only thing that makes sense to me at this point.
-
Can you try outdirection on the call to see if anything else will be logged? The exit code 2 just means it's a fatal error.
Steve.
-
Just for kicks, I created a SQL Job Agent and called the two .cmd files in their own job steps. I get the same result as with the SSIS package - step 1 retrieves the file just fine but step 2 does not extract the files. It says the job executed without errors and when I send results to a log file it is completely empty.
I'm at a complete loss here. I've never, ever had anything like this happen before and don't know what to do.
-
I should mention, too, that when I enabled logging it returns empty for the particular task in question. Aye...I have no idea how to resolve this. Usually stuff like this is fairly simple, i've been working on this for two damn days now.
I'm going to switch careers and become a shepherd.
-
Did you try using the cmdline version plus the outdirection of the 7zip log?
Steve.
-
Calling 7zip from the command line works, it extracts the file as expected. I have included 7zip in the list of environment variables. It also works when I create a .cmd file and run it on the command line. It DOESN'T work in a SQL Agent Job, which is a mystery to me - the account has permissions to the folder. The output file returns completely, 100% EMPTY. Unless i'm misunderstanding what you are telling me.
SSIS logs return nothing, as well.
What I don't get is WHY the Execute Process Task works by itself in SSIS, but not when I run the package. There is a disconnect here. Never had this unusual situation in SSIS or SQL Agent Job, ever.
-
So I took this out of SSIS and am using Powershell to do what I need. Seems to be working fine now. My organization prefers SSIS, but it's starting to slow me down.
Thanks for your help!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply