Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSIS package not completing when running from SQL Server Agent job Expand / Collapse
Posted Wednesday, July 20, 2011 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2011 8:48 PM
Points: 4, Visits: 9

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!

Post #1145119
Posted Wednesday, July 20, 2011 9:40 AM


Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
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.
Post #1145178
Posted Thursday, July 21, 2011 7:15 PM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 10:06 PM
Points: 374, Visits: 428
or remote into the server, doubleclick on the package (if its saved as DTSX) and run.
You will see the output

Post #1146396
Posted Tuesday, July 26, 2011 5:54 AM

SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 7:53 AM
Points: 45, Visits: 241
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.
Post #1148199
Posted Wednesday, July 27, 2011 8:33 AM



Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1149245
Posted Wednesday, November 02, 2011 1:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 01, 2013 3:05 AM
Points: 2, Visits: 33
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.
Post #1199109
Posted Wednesday, June 19, 2013 1:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 12:57 PM
Points: 1, Visits: 12
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:


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!
Post #1465342
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse