I'm somewhat new to SSIS and very new to Oracle so any pointers would be helpful.We're running SSIS 2008 with Oracle 10g source and destinations (using the Attunity connectors). The SSIS packages run correctly when executed manually and *usually* run on a scheduled basis correctly as well but every several days one of the packages never completes and instead errors out with ORA-03135 (connection lost).When the package runs correctly it takes about 15 minutes, and on nights it fails it shows as having a duration of over 6 hours culminating in the connection error above.I haven't seen anything unusual in the alert logs on the Oracle server but I'm not sure if I have all the necessary tracing on - any debugging tips or insight would be greatly appreciated.

Have a look in [msdb] .[dbo].[sysdtslog90] for anything strange.RegardsSteve, http://sqlug.be/blogs/steves_sql_blog/default.aspx

Hi Steve - thanks for the reply. I checked the sysssislog table (I guess this is sysdtslog90 in SSIS 2008), but didn't find any entries for these packages so I'm guessing package logging isn't being used....

As you said it fails at night.Could you check if there is any kind of activity thats happening in the ORACLE server at that time which is taking up the resources.Check the time when ur package is run.Hope this helps !! Sudeep| My Blog

This could be a bug in the Attunity connectors. Have you tried talking with them?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Sudeep - thanks for responding - there is very little other activity at that time and it doesn't appear that this would be the culprit.Cozyroc - not sure how this works.... We are using the Attunity connectors licensed by Microsoft for use in SQL Server Enterprise. I suppose you're right though, I could post to the Attunity forums.To be honest though, I suspect its an issue on our side.... I'm hoping to get some pointers on how to modify the ETL packages such that their progress and any intermediate errors would be more helpful in troubleshooting the problem. Or alternately, tips on how to monitor or automate something on the Oracle side to provide more insight.

Are you trying to connect to Oracle possibly when backups on it are running in Offline mode? Possibly the issue with, 'sometimes' is due to the backups running quicker/slower at times on the Oracle side.Ted Krueger Blog on lessthandot.com @onpnt on twitter

Hi,This is most likely related to locking issues on the destination table. Do you have multiple packages or maybe other processes loading the destination table? If yes then make sure that the destination component is configured to use array loading and not Fast Load. The Fast Load mode uses the Oracle Direct Path protocol which locks the destination table exclusively for the session.Regards,Gadi

Hi Gadi - thanks for the reply. There are no other packages that write or read from this particular destination table other than the intermittently failing package in question. I definitely agree that it feels like a locking issue of some variety.Thanks for the information on Fast Load vs array loading - I'll see if that has any effect.

Hi, I have the same issue: - SQL Server 2005 dts importing data from Oracle 10. After 3 hours the connection end with ORA-03135 Error, while it usually end in 15 minutes. - There aren't lock in suorce table in Oracle - There aren't lock in destination table. (I also tried to write data to a file) - The package fails randomly during the day and the night. - In log file I can see: OnPipelineRowsSent,... 22/09/2010 22.34.33,22/09/2010 22.34.33,0,0x,Rows were provided to a data flow component as input. : : 4299 : OLE DB Source Output : 16 : Sales : 29 : OLE DB Destination Input : 9958 OnPipelineRowsSent,... 22/09/2010 22.34.54,22/09/2010 22.34.54,0,0x,Rows were provided to a data flow component as input. : : 4299 : OLE DB Source Output : 16 : Sales : 29 : OLE DB Destination Input : 9958 OnError,... ,23/09/2010 1.35.44,23/09/2010 1.35.44,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-03135: connessione interrotta ". Do you have any ideas? thanks

Hi, I have some issues with Attunity connector for Oracle, Microsoft oledb oracle connector and OLEDB. I do not use them anymore. Attunity fails from time to time in production with time out error and there is no way to adjust the time out. Attunity did not respond to my suggestion to expose the time out property. Microsoft for Oracle is no longer supported and no longer works. Oracle provider for OLEDB sometimes fails in production 64 bit with unicode errors while it works in 32 bit debug mode. I could not pin point why this happen. It can work for months in a row and suddenly fail. or it can work in debug and not work in prod. ADO.Net Oracle Client Data Provider connectors will work all the time though. I am now exclusively using this connector. In my environment I must sometime crank-up the ADO.Net Oracle time out property to get it to always work at night when the Oracle server is heavily loaded. Especially with one of our datawarehouse and for some of its tables. I realize that my comment is somewhat vague, undocumented and imprecise. I am pulling from multiple Oracle datawarehouses, unicode or not, on various hardware or virtualized platforms and this makes the understanding of the data connectors specifics more difficult. What I know for sure is that I stay out of trouble when using the ADO.Net Oracle client no matter which Oracle source I pull from. Thanks/Regards, Philippe Cand