5

I've a a flat file which is pipe delimited as shown below:

header|header2
abc|abcdef
abc|abcdef

When I create a package on a 64 bit machine it always drops the last record. But on preview it shows both records.

Update:

If I do this it works:

header|header2
    abc|abcdef
    abc|abcdef
       |

Why is this and how do I fix it?

flybyte
  • 381
  • 2
  • 6
  • 9
  • 1
    Open the file up in a hex editor and look at the line endings. My assumption is the original file doesn't have cr/lf (0A 0D) on the last line. It would probably parse just fine without the trailing | row after you've hit enter. Confirm/Deny? – billinkc Sep 25 '11 at 01:47
  • @billinkc as per my question. I works on XP and not on Windows Server 2008 and SQL Server 2008 R2 is installed. I also tried hitting enter many times on last record or 2nd last record, still same issue. It looks like a bug –  Sep 25 '11 at 02:27
  • Are you sure you're not intentionally skipping the last row and all your rows have the same number of columns and delimiters? – Nick Chammas Sep 26 '11 at 14:22
  • It's just very very very wierd. I created a package on XP Machine to read exact same csv it works well. Imported the same package on Windows Server 2008 R2 and it just doesn't load the last record. Unbelievable – flybyte Sep 27 '11 at 01:06
  • same issue with csv, when deployed to sql r2 dropped last line of the text file. go to advanced column view, select all the columns and set the text qualification drop down to false for all the columns. additionally, we run our packages with the 32 bit setting on the execution tab of the step running the package. worked like a charm. –  Feb 17 '12 at 16:49

2 Answers2

5

I had the same problem on Windows Server 2008 R2 x64. A flat file source would not load the last record... So this answer helped me, and now it's working fine.

Go to advanced column view, select all the columns and set the text qualification drop down to false for all the columns.

Derek Downey
  • 23,440
  • 11
  • 78
  • 104
Linas
  • 51
  • 1
  • 1
4

I would guess the last record does not have a carriage return delimiter after it; try looking at the source file in a hex editor to verify.

SqlACID
  • 2,218
  • 16
  • 16
  • Hi @SqlACID, I just had a look at my csv file with hex editor and i can see OD and OA at the last line. But still the SSIS Package on Sql server 2008 R2 SP1 is not able to read it. If there are two records it just reads the first line. – flybyte Sep 26 '11 at 11:52
  • 1
    @flybyte Make sure you have all the latest service packs installed? – SqlACID Oct 04 '11 at 22:55