Zahid's blog on Data Engineering and BI

This problem was one of the most terrible one in my short SSIS career. I was using a script task which uses Oracle Oledb provider to connect to an oracle server. After the package was complete, I created a SQL server job and obviously the job’s step was configured to run from SQL Server Agent Service account. When, I started the job , it was hanging forever. There was no error message, no failure…nothing. The job simply hangs forever. I was fortunate enough to find out the solution. I went to the SQL server configuration manager. I went to the properties of SQL Server Agent and this service was logged on as a domain user account. I decided to run the job using the built-in account and chose “Local System” from the dropdown list of available built-in accounts. This time the job finished in time. It seems to be a permission/right problem as the job was trying to use a provider. Often, when an SSIS script cannot access configuration file or any other external resource due to permission problem, it hangs forever. May be, this was the case for my provider also.