Loading a csv file into sql server table

Question

Post reply

Loading a csv file into sql server table

Roust_m

SSCoach

Points: 17410

May 12, 2021 at 11:05 pm

#3882799

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.

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

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)

kaj SSCommitted Points: 1605 More actions · Answer 5

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.

Roust_m SSCoach Points: 17410 More actions · Answer 6

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.