Running bat files in SSIS

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

Does the batch file have any prompts that require user input?|||

What is SQL Agent Service account running with?

Is it the same account that you are your DBA use - when running the package manually?

Because when you run the job - it runs with the credential of SQL Agent account.

|||Phil, thxs 4 your reply. No prompts that will require user input. The bat file will do a ftp. Thanks again.|||

How do I check what is SQL Agent Service account running my job? I know when I run the pkg manually it will run under my ID.

I do own the SQL Agent job in the Development environment so the DBA told me that my ID should be trying to execute the bat file (as I said before my ID has the permissions so there is something very strange happening). She said when I move to MO it will be owned by SA.

I can't go to MO if it is not working in DEV

Thanks for your reply.

|||If you are using the SSIS job step to run the package, it runs under the service account that the Agent is set up to use, not the job owner's account. Try setting up a proxy account - check books online for details.|||

Hi,

It's simple. Use an Execute Process Task.

Double Click the Task, Under Process Tab, set the values for the following properties:

1) Executable = cmd.exe

2) Arguments = /c C:\BatchFolder\MyFile.bat.

Make Sure when you run the job, The Service account that runs your job should have access to this batch file.

Thanks

Subhash Subramanyam

|||

Jwelch -

There is a proxy account set by the DBA already... When we add a step to a job, for type we do "Operating system (CmdExec)" and for run as we do "SQLAgentJobProxy".

So... Do you believe this proxy account is the one with no access to run the bat file?

Thanks.

|||

That would be my guess.

Also, is the bat file located on a local drive, network share with UNC path, or a mapped drive?

|||Network share with UNC path.|||Might try copying it locally and see if that makes a difference.|||

I don't have Integration Services installed locally. I will check with the DBA on the permissions mentioned here and I will let you guys know.

Thanks.

|||

Folks,

The feedback I had from my DBA was that the SQLAgentProxyAccount does have access to execute the bat file in question... This is proved when I run a dts through the same job and the same bat file is fired successfully. She also agreed that the Executable Property in the Execute Process Task step was evaluating correctly.

So, our work-around was to remove the bat file execution from the SSIS pkg and add it to the second step of the job. Dirty and scary if you think that the ftp might not be the last step of a whole process. In my case, thanks to God it is... But since we have lack of time right now, this is the solution we put in place.

I would say the issue is still not clarified but I will mark this as closed and if somebody in the future runs into the same problem a new thread could be opened.

Thanks to all.