none
Cannot fetch a row from OLE DB provider "BULK" with bulk insert task

    Question

  • Hi, folks:
    I created a simple SSIS package. On the Control Flow, I created a Bulk INsert Task with Destination connection to a the local SQL server, a csv file from a local folder, specify comma delimiter. Then I excute the task and I got this long error message.
    [Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.".
    Wednesday, November 23, 2005 9:51 PM

Answers

  • Well..., congratulations. You found a bug in SSIS. I will file it and hopefully we'll improve it in SQL Server 2008. Thanks for the feedback. For now, as a workaround you can use Execute SQL task to do your bulk insert for native and widenative file types. Sorry for inconvinience.

     

    Thanks for your feedback,

    Evgeny Koblov,

    SQL Server Integration Services

    Tuesday, April 08, 2008 9:20 PM

All replies

  • I think I know the answer, if I bulk insert into a table of varchar(50) for all cilumns, then I am OK. But if I load the data into a table with, date, number etc. then it bombs. How do I specify the input format??
    Thursday, November 24, 2005 11:25 PM
  • Setting timeout to 0 worked for me...

    http://www.sqljunkies.com/WebLog/macaw/archive/2005/07/29/16264.aspx


    # re: Cannot fetch a row from OLE DB provider BULK for linked server (null) @ Wednesday, October 12, 2005 1:42 PM

    when the data flow starts the sql server destination starts waiting for output rows. the default is 30 secs. if you have any slow transformation tasks inbetween the source and destination you could intermittently or always get this. you can up the timeout or set it to 0 so it never timesout.

    Kristofor Selden

    Thursday, December 01, 2005 8:21 PM
  • i set the timeout to 0 , it sill doesnt work for me!!

    by time out  u mean the connect timeout for the connection manager ,rite ??

    Wednesday, September 20, 2006 6:51 AM
  • The best thing worked for me was just close the complete package project and reopen it and run it again.

     

     

    Saturday, December 15, 2007 2:39 AM
  • Having the same issue.   BCP works fine with the same format file, SSIS is too stupid to do the same thing.    The most simple task with SSIS always turns out to be so complicated that I hope I never have to do anything truly difficult with it.

    PLEASE please bring back DTS.   It worked.
    Wednesday, March 26, 2008 3:57 PM
  • Hi there,

    On my data Bulk Insert Task works fine when file contains data of different types. Can you give a sample of the file you use for Bulk Insert and describe how your Bulk Insert Task is configured? After that I will be able to investigate that.

     

    Thanks,

    Eugene Koblov,

    SQL Services Integration Team

    Tuesday, April 01, 2008 9:44 PM
  • I receive the following error message when I try to use the Bulk Insert Task to load data:

     

    Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".

    Task failed: Bulk Insert Task

     

    In SSMS I am able to issue the following command and the data loads into a table with no error messages:

    BULK INSERT TableName
        FROM 'C:\Data\Db\TableName.bcp'
       WITH (DATAFILETYPE='widenative');

     

    What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:

    DataFileType: DTSBulkInsert_DataFileType_WideNative

    RowTerminator: {CR}{LF}

     

    Let me know if you require any other information, thanks for all your help.

    Paul

    Monday, April 07, 2008 8:58 PM
  • Well..., congratulations. You found a bug in SSIS. I will file it and hopefully we'll improve it in SQL Server 2008. Thanks for the feedback. For now, as a workaround you can use Execute SQL task to do your bulk insert for native and widenative file types. Sorry for inconvinience.

     

    Thanks for your feedback,

    Evgeny Koblov,

    SQL Server Integration Services

    Tuesday, April 08, 2008 9:20 PM
  • Well..., congratulations. You found a bug in SSIS. I will file it and hopefully we'll improve it in SQL Server 2008. Thanks for the feedback. For now, as a workaround you can use Execute SQL task to do your bulk insert for native and widenative file types. Sorry for inconvinience.

    Thanks for your feedback, Evgeny Koblov, SQL Server Integration Services


    So does this mean Microsoft will only fix this bug in SQL Server 2008 and leave it unresolved for those who are stuck with SQL Server 2005 for the forseable future? In the real world, not all IT operations can make major DBMS version changes every few years as if we're putting in the latest version of Visio. Vendors drag their feet on certification, budgets are tight, etc...

    Keep the great new technology rolling in, but putting a stop to service packs too quickly (to conserve development resources, force customers to new versions, or whatever) is going to lead to significant customer backlash sooner or later.
    Thursday, August 27, 2009 7:10 PM
  • I got the same error with some additional error details (below).  All I had to do to fix the problem was set the Timeout property for the SQL Server Destination = 0

    I was using the following components:

    SQL Server 2008

    SQL Server Integration Services 10.0

    Data Flow Task

    OLE DB Source – connecting to Oracle 11i

    SQL Server Destination – connecting to the local SQL Server 2008 instance

    Full Error 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 Server Native Client 10.0"  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 Server Native Client 10.0"  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 Server Native Client 10.0"  Hresult: 0x80040E14  Description: "The Bulk Insert operation of SQL Server Destination has timed out. Please consider increasing the value of Timeout property on the SQL Server Destination in the dataflow.".

    For SQL Server 2005 there is a hot fix available from Microsoft at http://support.microsoft.com/default.aspx/kb/937545

    Thursday, December 10, 2009 6:42 PM
  • I also encountered this problem for SQL 2008. I found my answer here:

    http://blog.cybner.com.au/2007/09/cannot-fetch-row-from-ole-db-provider.html

    I was using a flat file source and SQL Server destination. Changing it to an OLEDB destination fixed it for me.

    Tuesday, April 13, 2010 6:43 PM
  • well, wish that was more challenging. When is the fix expected for release
    Friday, June 25, 2010 8:20 PM
  • Try this,, convert your staging table's datatype to nvarchar because i am running BULK INSERT via sql script and when i have my table data type to varchar(50) it want let me insert data into table but as soon as i convert to nvarchar(50) it worked.

    Here is my generic script

    BULK INSERT 'Mydb.dbo.MyTable'FROM "C:\temp\myfile.csv" WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")

     

    Good Luck.

     

    Thursday, April 28, 2011 2:16 PM
  • This problem is still not fixed on SQL 2008. I am running on SQL 2008 R2 and trying to execute it from SQL Agent Tasks and i have the error 80% of the times, but sometimes goes well. It is so annoying to know it is a problem with the Bulk Insert, not really with SSIS because I am running it as a stored procedure. I will change it to run as sp_execute but i am not confident it would work.
    Monday, May 30, 2011 10:09 PM
  • Hey,

    I got this error when executing Fuzzy Grouping and dumping the result into an SQL Server Destination (I believe this also uses a BULK Insert).

    I got around this problem simply by increasing the Timeout setting on the SQL Server Destination transformation (on the Advanced tab).

     

    Cheers,

     

    Radu

    Thursday, February 02, 2012 3:19 PM
  • For me, it was just a matter of working in Text Editor, and setting the Connection / Format to "Specify". I also switched to SQL Server OLEDB instead of Native Client OLEDB. I am no sure if that made a difference. Make sure your columns are wide enough too.


    +__+

    Monday, March 05, 2012 2:59 PM
  • Many Thanks.

    After a long search finally found some useful information.
    Thanks for your post. Keep it up.

    Friday, February 21, 2014 8:59 PM