Loading a csv file into sql server table

  • Hi,

    I've created a simple dataflow with flat file source and sql server destination.

    I am getting the following error messages:

    [MyDB MyTable table [2]] 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 11.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 11.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.".
    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "MyDB MyTable table" (2)
    failed with error code 0xC02020C7 while processing input "SQL Server Destination Input" (18). 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.
    There may be error messages posted before this with more information about the failure.

    [MyDB MyTable table [2]] Error: A commit failed.
    table [2]] Error: A commit failed. [SSIS.Pipeline] Error: table failed the post-execute phase and returned error code 0xC02092B5.

    And this informational message during the execution phase:

    [myfile CSV [1045]] Information: The total number of data rows processed for file "C:\Import\myfile.csv" is 2882.

    The file has 2881 rows (including the header).

    Any ideas?

    Thanks.

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • I avoid SSIS like the plague and so I'm not sure but it looks to me like SSIS isn't able to access the file.  Have you checked to make sure that SSIS has read access to wherever this file is stored?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This is confusing - because the first error appears to be trying to use a linked server.  There is no reason to use a linked server from SSIS - you should be connecting directly to the source or destination.

    My guess is that you have SSIS calling SQL and using something like BULK INSERT to try and load the file, but that is just a guess.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • One of the methods to read a CSV file is to make a "temporary" linked server, use it, then drop it.  I don't know enough about SSIS to say that it would do such a thing but I wouldn't totally discount it.  After all, SSIS is an app.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This is probably just contributing to the guessing game, but I'll ask anyway...

    Based on the difference in the number of records (your statement that the file has 2881 rows and the 2882 reported as processed): Does the last line of your csv input file by any chance contain an extra blank line at the end?

    The failing commit could indicate that the table you're inserting into has restrictions in place that will not allow blanks, for example and therefore rejects the row insert. Normally that would show up in the log as an SQL error message stating this, but since you're using a linked server, maybe not.

    Anyway, Notepad++ is your friend when inspecting the source file.

  • Yep, that exactly what it was.  Some junk at the end of the file.  I deleted few empty rows at the end in excel and it worked.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply