CODEWITHSUNDEEP
sql-server-2008ssisiooledbflat-file
buzzzzjay
buzzzzjay

Reputation: 1150

Why is SSIS OLE DB Import Not Importing Last Line of a text File?

I have created a basic Data Flow tasks in SSIS 2008 that is reading information from a a basic text file and importing it into a database. The file is Delimited with lines ending with {CR}{LF} and each field separated by a Vertical Bar {|}.

I have verified each line ends with a {CR}{LF} in my file that I am importing, but for some reason it doesn't import the last line in the file. If there is only 1 line it is not imported into the database.

In the File Connection Manager is shows all lines in the preview, in my current case 5 lines. Also, in the preview in Flat File Source Editor it shows all 5 lines, but in the OLE DB Destination the preview only shows 4 lines. Any idea what could be causing this? Thanks!

Upvotes: 2

Views: 2416

Answers (4)

Noman
Noman

Reputation: 11

I was getting the same with SQL Server 2008 R2. After a lot of searching & hair pulling found that installing SQL Server 2008 R2 SP2 cured the problem. Note that this bug is not part of the release notes for SP2 - but it sorts it out.

Upvotes: 1

Steven
Steven

Reputation: 1183

I believe this is a bug with SSIS. I tested it on two versions 10.0.5500.0 and 10.0.2531.0. This problem does occur in 10.0.5500.0 but does occur on the over version. To resolve the issue in older version I had to add an extra cr lf at the end of the file as well as setting the Text Qualified value to false as user1298950 wrote.

Upvotes: 1

user1298950
user1298950

Reputation: 29

See the last answer at SSIS is dropping a record on flat file source import. Setting the flat file object TextQualified value to false for all of the columns fixed the issue for me.

Upvotes: 2

cjk
cjk

Reputation: 46475

Sometimes to read these files properly, there needs to be a carriage return at the end of the last line, effectively creating a blank line at the end.

If the file isn't supplied like this, then you may need a script component to modify it.

Upvotes: 1

Related Questions