SSIS Package Hangs When Called From Windows Service

Question

Hi All,

I have an SSIS package that connects to an Oracle source using the OLEDB Source connector. I'm attempting to extract a large table by using multiple dataflows to query the Oracle data by partition and export it to a flat file destination. When I run the package via the command line using DTEXEC it runs fine. When I try to execute it from a Windows Service it hangs. The Windows service starts a process and issues the DTEXEC command. When I run DTEXEC via the command line I log on as the same user that is running the windows service. I turned logging on and found that its hanging in the pre-execute phase.

Any thoughts as to why it's hanging when called from the service but it works when executed via a command line?

I'm using SQL Server 2008R2

Thanks

Answer 1

Hi Joe,

It probably throws an error on the Oracle side, and it must be security related.

I think what would remedy is to ensure you run the process using the proper proxy that has rights adequate to what you run with when  the package executes from command line.

---

Arthur My Blog

Answer 2

DelayValidation property can help you

SSIS uses validation to determine if the package could fail at runtime. SSIS uses two types of validation.  First is package validation (early validation) which validates the package and all its components before starting the execution of the package.  Second SSIS uses component validation (late validation), which validates the components of the package once started.

---

http://uk.linkedin.com/in/ramjaddu

Answer 3

Thanks for your reponse ArthurZ. I thought the same initially, however, if in my data flow task I have 2 connections to the Oracle source it works fine.  If I have 3 or more it hangs.  The simple answer is to have no more than 2 connections in my data flow, but that still doesn't answer my question as to why it runs fine when executed from DTEXEC via the command line but hangs when called from the service.

Answer 4

Do not run them in parallel and the package must be good to go.

---

Arthur My Blog

Answer 5

Hi Joe Korn,

The windows service may be used by other processes, please open the Task Manager and check, you can refer to the following thread: http://stackoverflow.com/questions/6333013/problem-running-ssis-package-from-windows-service

Thanks,
Eileen

 

---

Eileen Zhao
TechNet Community Support