At more than one instances while working with my customers I found that there is a SSIS package which runs fine from BIDS as well as from command prompt using DTEXEC command but as soon as you try to execute this package as SQL Agent job, The JOB goes in a perpetual execution.
These are typical symptoms.
1. You have your SSIS package using Oracle OLEDB provider for one or more connection managers.
2. You have recently installed oracle client.
3. Your server hosting SQL Agent and Oracle client was not re-started after installation of oracle client.
4. SSIS package runs fine from BIDS as well as command prompt using DTExec utility.
5. SQL Agent job running the same SSIS package goes into perpetual execution, it neither finishes nor error out giving illusion of hang situation.
After your install oracle client, the information about oracle client installation directory and bin folder is stored in environment variables. Any application using oracle client would read the environment variables at its startup and would load needed dll from Oracle home directory. Services like SQL Agent which are running before the installation of Oracle Client will have old environment variable values as a result any thread spawned by SQL Agent will not be aware of the location of oracle client DLLs.
Ideally, any process not being able to load required DLLs would throw an error message in form of windows message box but since SQL agent is a service and not a console application it is not able to create a handle for this message box window. Result is, the error message box never shows up to the end users. This creates a deadlock: Job execution has thrown error message box which SQL agent cannot draw, and until this message box is drawn and the user clicks OK or Cancel button, The job execution won’t finish/terminate.
What to do ?
After installing Oracle client, Re-start your computer. If re-starting computer is not possible, Re-starting SQL agent should resolve the problem as process re-start would re-load new environment variable values with correct oracle installation path values.