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?

Improve this question
5
  • 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
    Commented Sep 25, 2011 at 1: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
    – flybyte
    Commented Sep 25, 2011 at 2: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
    Commented Sep 26, 2011 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
    Commented Sep 27, 2011 at 1: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.
    – user6790
    Commented Feb 17, 2012 at 16:49

2 Answers 2

Reset to default
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.

Improve this answer
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.

Improve this answer
2
  • 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
    Commented Sep 26, 2011 at 11:52
  • 1
    @flybyte Make sure you have all the latest service packs installed?
    – SqlACID
    Commented Oct 4, 2011 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.