SSIS Package runs long time as a SQL Agent Job

• Attopeu

  Ten Centuries

  Points: 1308

  More actions

  July 10, 2012 at 1:28 pm

  #392714

  Hi Guru,

  I have a SSIS package to export data to Microsoft Access on a file server. When I run this package on BIDS (on the server) the package runs very fast. However, when I set up a sql job then it takes a few hours to complete.

  Could it be something from SQL service or SQL Agent account authentication from SQL to the file server?

  Both File server and SQL server are on the same subnet.

  Thanks much,

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 11, 2012 at 7:15 am

  #1511519

  When you say you run it in BIDS on the server, do you mean the SQL Server or the file server?

  Since the job completes it doesn't sound like a permissions issue. If it were a permissions issue I would expect the job to fail.

  Can you setup some monitoring when the package runs as a job to see what might be the bottlenecK? Checking netwrork usage, CPU, IO's?

  THe other issue could be that someone or some process has the Access database locked and the package is waiting for it to become available.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• herladygeekedness

  SSCertifiable

  Points: 5878

  More actions

  July 12, 2012 at 9:37 am

  #1512142

  I'm wondering where it has to go to get to the Access db and whether there are more hops to it from the server vs your workstation.

  If the Acc file is instead local to the server, never mind ....