SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS package not completing when running from SQL Server Agent job


SSIS package not completing when running from SQL Server Agent job

Author
Message
John Miller-378867
John Miller-378867
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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.
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4140 Visits: 2629
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.
VALEK
VALEK
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 462
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
SinisterPenguin
SinisterPenguin
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 282
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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15109 Visits: 14396
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
Ranjit Nair
Ranjit Nair
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 42
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.
Dave Morin
Dave Morin
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 13
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!
srivastava.pankaj
srivastava.pankaj
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 2
perfect answer THANKS!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search