SSIS Bulk Insert doesn't load last line of data

Question

SSIS Bulk Insert doesn't load last line of data

Melanie Peterson

SSCrazy

Points: 2387
More actions
January 29, 2016 at 10:44 am

#324454

I have a csv file that I'm trying to load to a table in a SQL Server 2012 database. The table is empty to begin with. When I simply use the Import Data Wizard in SSMS, the file loads fine, but I need to automate this. So I've created an SSIS project with a Bulk Insert. Every time I run it, the last line of the csv file doesn't load. I've looked at the file in various text editors to see if there's some strange character at the beginning or end of the line, but I don't see anything, and SSIS doesn't return any error; it tells me everything went just fine. Does anyone know what could cause this behavior? Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

Hard to say without a copy of the data file and SSIS Package. Happy to repro on my side if you attach to this thread, or attach in a PM if you do not want it publicly posted.

Does the last line of the file terminate with a line break (or whatever your line terminator is for the rest of the file)?

In general I would recommend moving away from the Bulk Insert Task and move to a Data Flow Task with a Flat File Source and an OLE DB Destination with Fast Load enabled.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Tim ffitch SSCommitted Points: 1637 More actions · Answer 2

All I can think of is to make sure the last data line in the file is terminated with Cr/Lf or Lf depending which temination is used in the rest of the file.

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 3

Thanks, unfortunately, that's not it. Each line, including the last one ends with CRLF

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 4

Thanks, I've tried your suggestion about using a Data Flow, but that just makes things worse: the file won't load at all and my error handling component isn't trapping anything. The error I get in the Output window, though, suggests that I've got a column mismatch. Two things I've noticed: The first line of the file (the column headers) doesn't have a CRLF at the end of it, unlike all the other lines (including the last one) and my Data Flow source insists on putting in a "Column 38" where none exists, although I've unchecked it. I'm happy to send you this mess to look at if you're willing. I'll look into how; holler if you don't want to deal with this. Again, thanks.

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 5

Ah hah. I edit attachments. Here is my masterwork. Thank you for your help, both past and future. 🙂

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Melanie Peterson (2/1/2016)
Ah hah. I edit attachments. Here is my masterwork. Thank you for your help, both past and future. 🙂

I need a table definition for dayforce.EmployeeInformation.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 7

Melanie Peterson

SSCrazy

Points: 2387

February 2, 2016 at 7:00 am

#1855963

Here you go.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

You had an extra column in Flat File Connection Manager "HREXPORTTest Flat File" named Column38 that did not seem to belong there. I went into it and "Reset Columns" and it disappeared. The Package then ran fine for me and all lines in the file ended up in the tables.

One other comment, in that same Connection Manager you need to go to the Advanced Page and set the proper data types (including widths for strings) for each field coming in from the file. At present everything is setup to be read from the file as a 50-character string which could cause you problems later with truncation. In fact SSIS is trying to warn you on the OLE DB Destination that you could encounter truncation on the very first field-to-column mapping EmpNumber because the file is being read as if it is a 50-character filed and it is mapped to a VARCHAR(10). Basically, the work you need to do is all in the Connection Manager to the incoming csv file.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 9

Orlando, thanks so much for this, and sorry for the delay in getting back to you. I was on vacation in Puerto Rico. 😀 I saw the 38th Column, but didn't know how to get rid of it. Reset Columns was the key; everything's working fine now.

Again, thanks!