Monday, February 04, 2013 7:31 PMHi 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
Monday, February 04, 2013 7:42 PMModerator
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
Monday, February 04, 2013 7:45 PM
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.
Monday, February 04, 2013 9:54 PMThanks 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.
Friday, February 08, 2013 1:59 AMModeratorDo not run them in parallel and the package must be good to go.
Arthur My Blog
Wednesday, February 13, 2013 9:26 AMModerator
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
TechNet Community Support
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Monday, February 25, 2013 1:56 AM