SSIS Package hangs when called from Windows Service

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS Package hangs when called from Windows...

SSIS Package hangs when called from Windows Service

Rate Topic

Display Mode

Topic Options

Author

Message

Joe Korn

Posted Friday, February 01, 2013 11:05 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 03, 2013 8:08 AM
Points: 67, Visits: 675

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

Post #1414768

opc.three

Posted Tuesday, February 05, 2013 7:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:44 PM
Points: 6,703, Visits: 11,734

It could be any number of things but based on what you have stated it sounds like a good place to start would be with the Oracle driver and possibly the bitness of the environment.

Is the server 64-bit? Is your workstation? Is the service running under 32-bit or 64-bit? Are the Oracle drivers installed and configured on the server for the bitness the job is running as?

I know you said this is running via a Windows Service but the principles for running packages under SQL Agent are much the same. Here's a good resource with some usual suspects that you can draw from by ignoring the SQL Agent-specific verbage:

Troubleshooting: SSIS Package Execution Using SQL Server Agent (SQL Server Video)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1415824

« Prev Topic | Next Topic »

Permissions