I have an SSIS package that loads data nightly from a warehouse to a database. Both the SSIS and SQL Server agents are on SQL server 2012 Enterprise and Windows Server 2012 R2 Standard.

When I run the SSIS Package manually it takes less than a minute consistently.But when this package is executed by the scheduled job SQL Server Agent, it hangs for hours and hours and sometimes it does not even complete.

Here is my observation of the time it takes to run the package, after I restarted the Server (box). The scheduled job runs:

1st day 45 seconds --- pretty much the same time it takes to run when I run the SSIS package manually.

2nd day 2 hours and 25 minutes

3rd day 45 minutes

4th day 10 minutes

5th day ran more than 7 hours. I killed the schedule job and ran the SSIS package manually, it finished in less than one minute.

What would be the problem?

  • Fantastic Post the other day by Erik Darling that should answer your question: dba.stackexchange.com/questions/204565/… – MguerraTorres Apr 24 '18 at 19:11
  • @MguerraTorres thank you for the link i read it ,but it didn't help my issue.My problem is why it run less than a minute when I run it from SSIS repository consistently but when it run the scheduled job from SQL server agent the problem occurred? – SQL_DBA_Bini Apr 25 '18 at 14:16
  • When you start to treat SQL Agent/SSIS as an (external) Application, this should start making sense. If it looks and quacks like a duck, it's usually a duck. You're more than likely running into Parameter sniffing. – MguerraTorres Apr 25 '18 at 14:40
  • We really need a lot more information to help you, it's too vague for us to point you in a good direction. My best advice is to begin measuring. You know you are waiting longer on the package to execute in one method vs the other. Now measure what specifically is taking time. Enable SSIS logging or create your own, run SP_whoisactive regularly and look at the wait types, look at the job steps and history, where is it taking the time? Once you know X waited for Y and you can compare between the two scenarios, you and us can begin to make educated guesses. – Shaulinator Apr 25 '18 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.