2

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!

buzzzzjay
  • 1,140
  • 6
  • 27
  • 54
  • I checked and I do have SP2 Installed. I have had multiple other people look at it and it doesn't make since. It is a super basic read file import into database and doesn't make since why it misses the last line. Tried closing SSIS and that didn't help either. The only thing else I can think of is rebuilding it all and see if that helps. – buzzzzjay Sep 13 '11 at 18:09
  • I already have Notepad++ and double checked that it did have the {CR}{LF} at the end. That why it doesn't make since that it skips the last line. – buzzzzjay Sep 13 '11 at 18:58

4 Answers4

2

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.

Community
  • 1
  • 1
user1298950
  • 29
  • 3
1

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.

Steven
  • 1,143
  • 4
  • 10
  • 15
1

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.

Noman
  • 11
  • 1
1

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.

cjk
  • 45,739
  • 9
  • 81
  • 112
  • I tried editing the file and added an extra CRLF at the end and it didn't help. I even tried added multiple and it didn't change anything. I even tried coping the 1 line before the last line and it still didn't add it twice like it should of because there is no primary key in the table. – buzzzzjay Sep 13 '11 at 16:54
  • Did you eventually get this to work? From the comment above is looks like you are still having problems. I also have the this problem with no solution yet. Extra CRLF still not working. – doug Dec 05 '11 at 15:38