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 t开发者_如何学JAVAhat 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!

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.

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.

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.

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.