March 16, 2012 at 10:10 am
Does anyone have any advice regarding the following issue I am encountering:
I have an SSIS Package which uses the Oracle OLE DB driver to tranfer tables from and Oracle 11g database (Windows Server Dev and Prod Instances) to a SQLServer 2008 R2 database. The package was generated using the 64bit import/export wizard.
When I run the package on my desktop (Windows 7 64 bit) it works fine accessing either the develment or production Oracle instance.
However, when I run it on my Dev DB Server (SQLServer 2008 R2) it only works when I supply the credential for the development oracle db server. When I run it against the production server it fails after about 10 seconds - after transferring between 5 to 10 tables.
What is really strange is that if I run the job again, it fails on a later step.
Below is an extract from my logfile:
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,Server1,Operator1,DWH_Refresh,{6D807CF7-9921-47DE-9636-1535B4EE6D41},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:00 AM,3/16/2012 11:31:00 AM,0,0x,Beginning of package execution.
OnPreExecute,Server1,Operator1,DWH_Refresh,{6D807CF7-9921-47DE-9636-1535B4EE6D41},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:00 AM,3/16/2012 11:31:00 AM,0,0x,(null)
OnPreExecute,Server1,Operator1,Get list of Schemas to process,{EA37B7BD-63FC-47A1-B56D-5932E8AEA8FD},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:00 AM,3/16/2012 11:31:00 AM,0,0x,(null)
OnPreExecute,Server1,Operator1,For Each Schema,{5D06C3AF-7780-48BB-9ADF-B682BA78D2C9},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:01 AM,3/16/2012 11:31:01 AM,0,0x,(null)
OnVariableValueChanged,Server1,Operator1,DWH_Refresh,{6D807CF7-9921-47DE-9636-1535B4EE6D41},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:01 AM,3/16/2012 11:31:01 AM,0,0x,strSchemaName
OnPreExecute,Server1,Operator1,Delete All Data,{17C9F184-1EA1-4076-8254-D25668FD867D},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:01 AM,3/16/2012 11:31:01 AM,0,0x,(null)
OnPreExecute,Server1,Operator1,Execute Package to load data,{21B3B1F4-BF3E-4C8E-96B1-B532EADC7453},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:01 AM,3/16/2012 11:31:01 AM,0,0x,(null)
User:PackageStart,Server1,Operator1,CMHS06,{9FECE982-400B-4BC0-BCF5-DFD111AEE8A6},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:01 AM,3/16/2012 11:31:01 AM,0,0x,Beginning of package execution.
User:OnPreExecute,Server1,Operator1,CMHS06,{9FECE982-400B-4BC0-BCF5-DFD111AEE8A6},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:01 AM,3/16/2012 11:31:01 AM,0,0x,(null)
User:OnPreExecute,Server1,Operator1,Data Flow Task 1,{5DA91A1F-DAEE-433A-B3EF-71104343A60D},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:01 AM,3/16/2012 11:31:01 AM,0,0x,(null)
User:OnPreExecute,Server1,Operator1,Data Flow Task 2,{DFA28D6A-11A5-4EE6-96EE-28F25C688066},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:02 AM,3/16/2012 11:31:02 AM,0,0x,(null)
User:OnError,Server1,Operator1,Data Flow Task 2,{DFA28D6A-11A5-4EE6-96EE-28F25C688066},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:03 AM,3/16/2012 11:31:03 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
User:OnError,Server1,Operator1,CMHS06,{9FECE982-400B-4BC0-BCF5-DFD111AEE8A6},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:03 AM,3/16/2012 11:31:03 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
OnError,Server1,Operator1,Execute Package to load data,{21B3B1F4-BF3E-4C8E-96B1-B532EADC7453},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:03 AM,3/16/2012 11:31:03 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
OnError,Server1,Operator1,For Each Schema,{5D06C3AF-7780-48BB-9ADF-B682BA78D2C9},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:03 AM,3/16/2012 11:31:03 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
OnError,Server1,Operator1,DWH_Refresh,{6D807CF7-9921-47DE-9636-1535B4EE6D41},{DB523D78-1F47-4561-BD86-B61903A4A690},3/16/2012 11:31:03 AM,3/16/2012 11:31:03 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
March 18, 2012 at 12:26 am
Are you saying it works when running on your dev server against the dev oracle database, but fails when running on the dev server against your prod oracle database? Or do you have a seperate prod server that it is failing on?
So as far as you know the only difference between a working and failing case is the source oracle server?
Perhaps you could try and add more logging info. I don't see any ORA errors in there, which implies it is SSIS failing rather than Oracle breaking the connection.
March 28, 2012 at 7:56 pm
I managed to solve the problem. I ran a trace on the oracle server and discovered that my SSIS package was encountering a network error.
I explained my problem to our network engineer and he found that the SQL ALG was enabled on our Juniper firewall. Once this was disabled my package ran fine.
March 28, 2012 at 8:00 pm
Thank you for responding.
March 29, 2012 at 2:19 am
Thanks for posting your solution. You have contributed to the data quality of the internet!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply