Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Large Imports Fall over AKA Cannot fetch a row from OLE DB provider "BULK" for linked server


Large Imports Fall over AKA Cannot fetch a row from OLE DB provider "BULK" for linked server

Author
Message
Chris Hewitt-261101
Chris Hewitt-261101
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
Have you been able to resolve the problem?

We are having similar issues.

Thanks,
m
vishal.gamji
vishal.gamji
Mr or Mrs. 500
Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)

Group: General Forum Members
Points: 544 Visits: 532
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.
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
Yes, All data related concerns have been ruled out. The issue does not happen every time. The data is always the same.

Thanks,
m
vishal.gamji
vishal.gamji
Mr or Mrs. 500
Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)

Group: General Forum Members
Points: 544 Visits: 532
Wht abt sending the error rows to a file and reviewing them?
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
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
Chris Hewitt-261101
Chris Hewitt-261101
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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
vishal.gamji
vishal.gamji
Mr or Mrs. 500
Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)

Group: General Forum Members
Points: 544 Visits: 532
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)
mishka-723908
mishka-723908
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 241
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
extra11
extra11
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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 :-(
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search