Execute process task - doesn't run exe

Hi,

I am currently developing completely on a local machine and have a DTS package that on completion should run a local exe file that compresses the csv files that were just imported.

If I run the package in the designer or directly then all is fine and the files are compressed.

If I run the file from SQL Agent as a scheduled task or manually start the task then the DTS runs fine and completes successfully but the files are not compressed.

I also tried pointing the step at a basic bat file that deletes the csv files. This doesn't run either.

I don't think there are any network access issues as everything is running from local disks and the data is imported from the same directory as where I want to run the exe.

I have tried running my SQLSERVERAGENT service as .\Administrator and also as Local Service Account without success.

Any pointers on how to achieve running a simple exe or bat file would be greatly appreciated.

Thanks,

Matt

Tried start sql agent with local system? Who's job owner? Is it sysadmin?

Hi, thanks for the reply. The job owner is sa

Tried run .exe in job step directly?

Yep - i've tried running the step in the designer, running the package from the designer and running the package from the list of packages in SQL management software.

The only time it doesn't work is when the DTS is initiated from the SQL Agent list - either manually or as a scheduled job.

As it is all local, and the data is imported from the same directory as the exe file I don't think that it is an issue with DTS not being able to find the exe.

I am guessing that it comes down to a permissions issue with SQL Server Agent user but that is using the local admin account currently so I don't know why there would be a permissions issue.

Has anyone successfully got a DTS package to run an exe or bat file?

Thanks

Again, did you try run .exe along in sql job step outside of package?

I think I must have misunderstood you.

I have set up a new SQL job, with a type of "Operating System Command (CmdExec)" and in the Command window I have entered the full path to the exe. Is this what you were suggesting?

I tried running the job and nothing happened. Tried the exact same command in the Run command and the files were processed.

Thanks

That means sql agent is unable to handle that .exe, you can't run interactive program in sql job. Winzip has dos addin that works in sql job.

Thanks for your help

Ill investigate options to rewrite my .Net console app to not be interactive. I guess changing it to a winforms app with no forms might work.

Thanks again

Good Luck and Happy SQLing.