Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Large Imports Fall over AKA Cannot fetch a row from OLE DB provider "BULK" for linked server Expand / Collapse
Author
Message
Posted Wednesday, December 10, 2008 4:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 1, 2013 4:58 AM
Points: 28, Visits: 61
Hi there,

I am importing a fairly large text file - abour 0.5GB and a few million rows.

After about a million rows the package falls over with the following message:

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

I have seen postings in other forums and the issue seems to refer to time out/commit size problems. I have tried using both SQL Server and OLEDB destinations with the following settings:

SQL Server:
Timeout: 0
MaxInsertCommitSize: 50000

OLEDB
Maximum Insert Commit size: 50000

The above settings have not worked, and I am still unable to get my data loaded.

Any ideas????

Many thanks!!!
Chris
Post #616847
Posted Thursday, April 16, 2009 11:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
Have you been able to resolve the problem?

We are having similar issues.

Thanks,
m
Post #698740
Posted Thursday, April 16, 2009 12:20 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
Did u rule out any data type conversions (like for ex: coming from DB2 source to SQL Server dest, even though the data type is datetime or similar on both sides SQL Server handles dates after a certain year (1873 or something like that, so a date value like 01/01/0001 bombs in the ETL).

Watch for any inconsistent data types... that would be the first place i would look. Let me know how it goes.
Post #698749
Posted Thursday, April 16, 2009 12:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
Yes, All data related concerns have been ruled out. The issue does not happen every time. The data is always the same.

Thanks,
m

Post #698754
Posted Thursday, April 16, 2009 12:29 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
Wht abt sending the error rows to a file and reviewing them?
Post #698759
Posted Thursday, April 16, 2009 1:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
We tried that in the past as well. We did not see anything specific to those rows. We are able to load the same files on 2nd try's.
Thanks,
m
Post #698848
Posted Friday, April 17, 2009 5:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 1, 2013 4:58 AM
Points: 28, Visits: 61
Hi Guys

Same deal for us - data issues have been ruled out, there is nothing visibly wrong with the rows, and inconsistent when fall over, using exactly the same data set

Chris
Post #699295
Posted Friday, April 17, 2009 10:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
Can u specifically isolate the rows (specifically the first specimen) that is causing the problem. I would say profile the data. use this one row in your load process (i know u said that there are no data probs...but doesnt harm to verify again)

Post #699573
Posted Friday, April 17, 2009 10:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 3:26 PM
Points: 54, Visits: 195
In regards to Data, it has been verified at a minimum more than 50 times.

This is definitely not a data issue. There is something else going on.

Thanks,
m
Post #699579
Posted Thursday, June 25, 2009 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 24, 2013 6:43 AM
Points: 3, Visits: 40
I believe this may be due to memory configuration issues, any luck with a fix?
I'm running MSSQL 2005 Standard Edition ver. 9.00.4035.00 (SP3) on Windows Server 2003 32bit and a similar problem arises in a number of cases, including OPENDATASOURCE and OPENQUERY towards Access databases and Excel sheets, BULK INSERTs with linked servers and CLR procedures: pretty much everything that can be classified as an external data source, involving the use of external dlls. This is slowly driving me mad, looks like it has something to do with memory management, although I am currently using AWE and reserving ~6 GB for the sql service... Things work fine until the process reaches a certain amount of allocated memory, which is usually around 4GB but can sometimes be ~1.5GB; unfortunately, restarting the service appears to be my only option at the moment :-(
Post #741883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse