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?
