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
Author
Message
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
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.
Post #1145119
Posted Wednesday, July 20, 2011 9:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
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


M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
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: Saturday, August 2, 2014 4:08 PM
Points: 45, Visits: 242
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 7,135, Visits: 12,748
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
Post #1149245
Posted Wednesday, November 2, 2011 1:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 1, 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:

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

Add to briefcase

Permissions Expand / Collapse