December 10, 2008 at 4:45 am
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
April 16, 2009 at 11:54 am
Have you been able to resolve the problem?
We are having similar issues.
Thanks,
m
April 16, 2009 at 12:20 pm
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.
April 16, 2009 at 12:25 pm
Yes, All data related concerns have been ruled out. The issue does not happen every time. The data is always the same.
Thanks,
m
April 16, 2009 at 12:29 pm
Wht abt sending the error rows to a file and reviewing them?
April 16, 2009 at 1:42 pm
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
April 17, 2009 at 5:42 am
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
April 17, 2009 at 10:21 am
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)
April 17, 2009 at 10:28 am
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
June 25, 2009 at 9:00 am
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 🙁
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply