SSIS package not running when called as step in SQL Job

Question

I have a .dtsx file (an SSIS package) that downloads files from an FTP server and imports data. It runs fine whenever I run it manually. However, when I schedule calling the package as a step in a SQL server agent job, it fails. The step it fails at is the one where I call a .bat file. The error in the job history viewer says this:

Error: 2009-05-26 12:52:25.64
Code: 0xC0029151 Source: Execute batch file Execute Process Task
Description: In Executing "D:\xxx\import.bat" "" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1).

I think it's a permissions issue, but I'm not sure how to resolve this. The job owner is an admin user, so I've verified they have permissions to the directory where the .bat file is located. I've tried going into Services and changing the "Log On As" option for SQL Server Agent, and neither option works (Local System Account and This Account). Does anyone have ideas as to what other permissions need to be adjusted in order to get this to work?

Answer 1

Are you executing the SSIS job in the batch file, or is the batch file a step in the SSIS control flow?

I'm assuming the latter for this answer. What task are you using to execute the batch file (e.g. simple execute program task or a script task). If the latter, it looks like your batch file is actually failing on some step, not the SSIS script. I'd check the permissions of what your batch file is trying to access

In fact, it might be a better idea to rewrite the batch file as a script task in SSIS, because you'll get much better error reporting (it'll tell you which step in the script fails).

You could try executing the batch file using the runas command in a command window. If you try and execute it under the local system or network system account, it should give you a better error. If it does error, you can check the error level by going "echo %ERRORLEVEL%".

If it wasn't the latter, and you're executing the SSIS package via a batch file, why?

Answer 2

Are you possibly accessing a mapped drive in your .bat file? If so, you can't rely on the mapped drive from within the service, so you'd have to use UNC path.

Answer 3

I tried executing just the batch file as a SQL Job step, and it gave more specifics. It showed that it failed when I was trying to call an executable, which was in the same directory as my .bat file, but not in the windows/system32 directory, which is where it was executing from.

I moved the executable to the system32 directory, but then I had no clue where my files were being downloaded to. Then I found that there's a property for the Execute Process Task (the one that executes the .bat) called WorkingDirectory. I set this to be the directory where the bat is located, moved the executable back into the same one as the .bat file, and it's now working as expected.