July 20, 2011 at 8:57 am
Hello!
I have an SSIS project with one step, an Execute Process Task. It simply runs an executable off the hard drive and passes in three parameters. From debug mode, it kicks off the executable perfectly and the step is completed.
However, when I publish the packing into the SQL SSIS package store and create a job with SQL Server Agent, it runs and generates no error messages but it never actually runs the executable. It just hangs there with its wheel turning forever, if I let it. I have to manually stop the job to get it to quit.
I've been looking through the properties for the SSIS project and for SQL Server Agent but cannot find anything that might cause it to hang up like this. Any advice would be appreciated!
TIA.
July 20, 2011 at 9:40 am
One of the great advantages of SSIS is the ability to add robust error handling to sql server jobs. I would suggest adding some error handling, or at least logging to your job and see what comes out.
July 21, 2011 at 7:15 pm
or remote into the server, doubleclick on the package (if its saved as DTSX) and run.
You will see the output
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
July 26, 2011 at 5:54 am
Hi - One difference by running your package as a job is that by default it is now running in the context of the SQL agent Service account where as in debug it is running as your user account.
Are you sure the user running the SQL Agent Service has rights to access & run the executable?
Perhaps try running the package in debug mode while logged in as the SQL agent user (if possible)?
Are you using the full path to the exe?
Hope this is useful.
July 27, 2011 at 8:33 am
Chances are it is not a permission issue related to reaching the executable or related to something the SSIS package or executable are trying to do otherwise the job would simply fail. That said, it could be related to the aforementioned fact that the packge is now going to be running under the context of either the proxy account the job step is set to run as, or the SQL Agent service account.
What does the executable do?
I have seen lots of posts with similar descriptions where the root cause is attributed to something the package is doing, either directly or indirectly, that generates an interactive prompt. Since this is an unattended process there is no one to answer the question in the prompt so the package just sits there indefinitely. Examples of this scenario are SFTP and PGP. When you first kick off a process that requires the use of an encryption key you might be prompted to accept the use of the keys so they can be added to the current user's keyring. In this instance, if the user running the SSIS package via the SQL Agent job has never used these keys then a prompt will be generated.
The fix is to log in as the user running the package via the job, and then run the command line in the job step interactively to see if there are any prompts or messages.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 2, 2011 at 1:19 am
If you are using your executable from a UNC path you will have to add it in the zone setting in Local intranet.
On your Internet Explorer, go to Security and select Local Intranet setting. Click on Sites and add your UNC path and click ok. The file:// protocol is added automatically.
This has nothing to do with SSIS, but everything to do with the zone settings in Internet Explorer.
This should help.
June 19, 2013 at 1:00 pm
I realize this is a VERY old thread but figured i'd post the solution to thsi problem as I just dealt with the SAME exact problem.
Call your EXE in the Process task via a .Bat file.
on the line before you call your EXE, put the following:
SET SEE_MASK_NOZONECHECKS=1
It seems that the SQL Agent service uses the Zone Checking when executing an external EXE. Setting this environment variable will bypass this.
After I did this, everything worked like a charm!
September 21, 2016 at 1:56 am
perfect answer THANKS!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply