Hi, I have an SSIS package which runs on SQL SERVER 2005, pulls data from ORACLE and places it into my SQL SERVER 2000 data warehouse. My package, which is scheduled in windows scheduler fails with the following error: Error: 2008-10-10 22:54:56.39 Code: 0xC0202009 Source: DATA FLOW TASK NAME OLE DB Destination [187] Description: An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot sort a row of size 8695, which is greater than the allowable maximum of 8094.".End Error Error: 2008-10-10 22:54:56.39 Code: 0xC0047022 Source: DATA FLOW TASK NAME DTS.Pipeline Description: The ProcessInput method on component "OLE DB Destination" (187) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.End ErrorError: 2008-10-10 22:54:56.39 Code: 0xC0047021 Source: DATA FLOW TASK NAME DTS.Pipeline Description: Thread "WorkThread0" has exited with error code 0xC0202009.End Error The Data Flow objects works fine when executed in debug mode in Visual Studio. But it fails when the entire Package is initiated as a task, in windows scheduler. I get this error regularly but not necessarily with the same DataFlow task. It is different at every run. What could be causing this to fail? Is it that it is failing because it is trying to pull huge data (millions of rows)? How can this be resolved?

In SQL 2000 there is row size limitation of 8060 bytes. http://blogs.msdn.com/msdnts/archive/2006/12/01/row-size-limitation-in-sql-2000-and-2005.aspx So the problem is not because of no. of rows you are trying to insert but size of some particular row which exceeds the limits of SQL 2000. HTH ~Mukti

Thanks for the clarification. What can be done to get rid of this error, if you don't want to normalize? Iam pulling 3 fields of CLOB data type from ORACLE along with some other fields and placing them into my 2000 server Warehouse. This is how my destination table (in MS SQL 2000 Server) looks like: CREATE TABLE Table1( CLOB_AT_SOURCE1 ntext, col2 nvarchar(40), col3 nvarchar(50), col4 nvarchar(130), col5 nvarchar(40), col6 nvarchar(40), col7 datetime, col8 nvarchar(20), col9 datetime, col10 datetime, col11 nvarchar(30), col12 datetime, PRI_KEY1 nvarchar(80), col14 datetime, col15 nvarchar(40), col16 nvarchar(40), col17 nvarchar(50), col18 nvarchar(40), CLOB_AT_SOURCE2 ntext, col20 nvarchar(40), col21 datetime, col22 nvarchar(50), col23 nvarchar(40), col24 nvarchar(130), CLOB_AT_SOURCE3 ntext, col26 datetime, ) I am using dbms_lob.substr () function to convert from CLOB toVARCHAR while pulling (as a part of DataReader Source code). I tried using the data types Varchar(2000), Nvarchar(2000), Text and then Ntext for the columns 'CLOB_AT_SOURCE1', 'CLOB_AT_SOURCE2', 'CLOB_AT_SOURCE3'. I would not like to break up this table for some reasons. Is it possible to achieve this without breaking the destination table? Thanks, Monika.

I think text/ntext will work. Try converting it beforehand to dt_text or dt_ntext and then insert it.(derieved column?)

Nitin, Can you elaborate? Iam kind of new to this. Please explain this to me in detail. I'll be grateful.

use a derieved column (or data conversion) componentand select the output datatype asdt_text. (its the same length as CLOB in oracle) and then try inserting it. sorry, i can't regenerate the case here (no oracle!!)but this should work. post back the errors if any...

Are all your cols unicode as i see you are using nvarchar? Try using varchar where unicode is not required. Bear in mind that nvarchar storage size 'n' in bytes, is two times the number of characters entered. So twice as much as varchar. HTH

My destination is ntext and now it is failing with another error: Error: 2008-10-21 03:30:20.02 Code: 0xC02090F5 Source:DATAFLOW TASK NAMEDataReader Source 1 [4459] Description: The component "DataReader Source 1" (4459) was unable to process the data.End Error Ihave beenexecuting this from last 3 days and i noticed that it is failing at different modules everytime but with the same error. what is causing this ?

Mukti, Iremoved the unicode data types i.e all the NVARCHARs are now VARCHARs and all the NTEXTs are now TEXTS. It still did not work. Following is the error: Error: 2008-10-21 04:49:58.97 Code: 0xC02090F5 Source:DATAFLOW TASK NAMEDataReader Source [148] Description: The component "DataReader Source" (148) was unable to process the data.End Error Please help me with this. I'll be grateful.

Hi, I am now able to get rid of this error. Iused linked servers concept andI can pull my data without any issues. Thank you all for your sugestions. Those indeed helped me.

Hi Monikak, I am having a same issue, Could you please share your experience how you fix this problem. It would be great for us. Thanks. Rocky.