Post reply

SSIS Execute Process task timeout when run under SQL Agent

  • September 12, 2007 at 10:35 pm

    #180832

    Hi all.

    I've set up a simple package that takes an Access database, compacts it, zips it up and mails it.

    It works fine when I run the package from within BIDS.  Takes mere seconds.

    I have deployed the package as a SQL Server package, and can execute the package from within SSMS (on the server, rather than my workstation - workstation returns an error saying that edition of SQL is not high enough. SQLExpress).  Again, it takes seconds and works great.

    I now try and schedule a job to execute this package.  The job completes, but doesn't do what is expected.  Closer analysis of the history shows that the "Compact db" task timed-out.  The rest of the package then doesn't run, but for whatever reason, the job reports sucess!

    I then disable the "Compact db" process task, rebuild and re-deploy the package.  SQL Agent runs the package perfectly.  i.e. database is zipped and mailed.  It just happens to be twice the size it would otherwise be if it had been compacted.

    It is this "Compact db" task that is causing the problem. 

    SQL Agent has local server admin priveliges.

    SQL 2K5 SP2 64-bit

    Any suggestions gratefully received, as I'm pulling my hair out, and I haven't much left.

    Cheers

    Simon

  • September 14, 2007 at 4:42 am

    #733706

    is you run the package on the server through BIDS using the same user as Agent is running as?

     

    Sounds like a security type issue. What does Compact DB task do? Could there be a dialog waiting for user input?

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • September 16, 2007 at 10:23 pm

    #734002

    Hi Crispin,

    Firstly, to answer your question, "Compact db" simply runs the MSAccess .exe from the command line on the server with the option to compact the .mdb file.

    Now, your two suggestions prompted me to log on as the Agent user (which I hadn't previously tried...) and see what was happening when I ran the package as that user.

    The answer appeared immediately!  I hadn't run MS Access under that user before, and Access needed configuring before use.  (It must have been displaying a dialog as you suggested.)

    So, thanks for your help on that, the job now works perfectly!

    It's so often something obvious and simple! 

    Cheers

    Simon

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply