none
Missing last row when package is run as a job from SQL 2008 R2

    Question

  • I have one package which reads a CSV and fills a table. This is a simple extraction. When i add the package to the server storage (SQL 2008), make a job en run it from that job, there is no problem.   

    When I make the same job at our SQL 2008 R2 server and i run it, i'm missing the last row. It happens with all CSV's. When i run the package directly from the storage, there is no problem. So it only happens when its started as a job.

    Is this a bug? Or am i missing something?

     

    Thanks in advance!

    J2.0

     

     

    Friday, May 27, 2011 8:50 AM

Answers

  • I found out just now that when i add a text qualifier (just a random one i guess), the package runs correctly. All rows are in the table. My flatfile does not contain any text qualifiers. My guess is that the file is read as text and the CRLF is not reconized at the last line.

     

     


    Tuesday, June 07, 2011 2:55 PM

All replies

  • That certainly is odd. The last row and the one before that, does it have the correct row delimiters?
    MCTS, MCITP - Please mark posts as answered where appropriate.
    Friday, May 27, 2011 8:53 AM
  • Thanks for the fast reply. The delimiter is set as {CR}{LF}. This is conform the file.
    Friday, May 27, 2011 9:13 AM
  • Not solved yet!

    Anyone any idea's?

    Monday, May 30, 2011 9:21 AM
  • For all your CSV's what is the number of rows? do they have huge rows or with fewer rows?
    Thanks Karthikeyan Anbarasan www.F5Debug.net
    Monday, May 30, 2011 10:07 AM
  • Are you aware that when you "run the package directly from storage" you are actually running it from your machine, and not on the server itself?  This can result in many execution differences.

    Have you enabled Package Logging?  Please do.

    Do you have any error redirection in your Data Flow?


    Todd McDermid's Blog Talk to me now on
    Monday, May 30, 2011 1:39 PM
    Moderator
  • Thanks so far. I wasn't realizing that i was running from my machine instead of the server. I executed the package from  the server resulting in the same (last) row missing. I did notice a warning in the logging saying: "Warning: There is a partial row at the end of the file"

    I started searching and found a bunch of the same problems but no awnsers i.e.:

    http://int.social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/510b9dbe-1e76-4d3b-8dad-aaf292a2c0fa

    Tuesday, May 31, 2011 1:33 PM
  • Please use an editor that can display hidden characters (we want to inspect the row endings at the bottom of the file).

    Notepad++ would be one of them, from its View command in the toolbar go to Show Symbols->Show All Characters.

    Please paste an image of how the records end here.


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 31, 2011 1:49 PM
    Moderator
  • I tried different endings:

    last 3 lines:

    AO ;;;5728;1,1;2011;10;11.800[CR][LF]
    AO ;;;3758;1,1;2011;11;11.800[CR][LF]
    AO ;;;7538;1,1;2011;12;11.800[CR][LF]

    I also tried:

    AO ;;;5728;1,1;2011;10;11.800[CR][LF]
    AO ;;;3758;1,1;2011;11;11.800[CR][LF]
    AO ;;;7538;1,1;2011;12;11.800

    and

    AO ;;;5728;1,1;2011;10;11.800[CR][LF]
    AO ;;;3758;1,1;2011;11;11.800[CR][LF]
    AO ;;;7538;1,1;2011;12;11.800[CR][LF]
    [CR][LF]

     

    Tuesday, May 31, 2011 2:29 PM
  • I tried different endings:

    last 3 lines:

    AO ;;;5728;1,1;2011;10;11.800[CR][LF]
    AO ;;;3758;1,1;2011;11;11.800[CR][LF]
    AO ;;;7538;1,1;2011;12;11.800[CR][LF]

    I also tried:

    AO ;;;5728;1,1;2011;10;11.800[CR][LF]
    AO ;;;3758;1,1;2011;11;11.800[CR][LF]
    AO ;;;7538;1,1;2011;12;11.800

    and

    AO ;;;5728;1,1;2011;10;11.800[CR][LF]
    AO ;;;3758;1,1;2011;11;11.800[CR][LF]
    AO ;;;7538;1,1;2011;12;11.800[CR][LF]
    [CR][LF]

     


    What input is given to you (that you need to process)?

    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 31, 2011 2:34 PM
    Moderator
  • What exacly do you mean? Its an excelfile which is saved as csv.

    Tuesday, May 31, 2011 2:37 PM
  • If you save it manually you supposed to get

    AO ;;;7538;1,1;2011;12;11.800[CR][LF] format

    So how about we stick to processing this kind of file?


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 31, 2011 2:43 PM
    Moderator
  • If you save it manually you supposed to get

    AO ;;;7538;1,1;2011;12;11.800[CR][LF] format

    So how about we stick to processing this kind of file?


    Arthur My Blog
    By: TwitterButtons.com

    So if you agree with the above, lets run this file off the file system on the SQL Server 2008 R2 box. Do you get any errors or warnings?

    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 31, 2011 2:45 PM
    Moderator
  • If you save it manually you supposed to get

    AO ;;;7538;1,1;2011;12;11.800[CR][LF] format

    So how about we stick to processing this kind of file?


    Arthur My Blog
    By: TwitterButtons.com

    So if you agree with the above, lets run this file off the file system on the SQL Server 2008 R2 box. Do you get any errors or warnings?

    Arthur My Blog
    By: TwitterButtons.com
    Just the warning: "Warning: There is a partial row at the end of the file"
    Tuesday, June 07, 2011 11:49 AM
  • Means you have an extra CRLF that perhaps you can just cut off with a derived column transformation.
    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, June 07, 2011 1:28 PM
    Moderator
  • I found out just now that when i add a text qualifier (just a random one i guess), the package runs correctly. All rows are in the table. My flatfile does not contain any text qualifiers. My guess is that the file is read as text and the CRLF is not reconized at the last line.

     

     


    Tuesday, June 07, 2011 2:55 PM
  • Hi,

    This solution does not work in SQL 2008 R2. Anyone has solution to this problem on R2?

    Thanks,

    Yogesh


    http://sqlworkday.blogspot.com/
    Saturday, December 17, 2011 4:26 AM
  • I had the exact same problem, and tried all kinds of things - your idea of adding text qualifier worked 100%.

    Thanks!!

    Thursday, January 26, 2012 12:09 PM
  • Hi:

    I have the same problem. I have 26 rows of data in my CSV file but only the first 25 get imported.

    When I preview the data in the Flat File Connection object, I see all 26 rows.

    What do you mean by adding a Text qualifier? I added couple of blank rows but that did not resolve the issue.

    venki

    Friday, January 11, 2013 2:33 AM
  • In the Connection Manager, in your SSIS package, open the File Connection you configured to read the CSV files. In the properties window, there's a property called Text Qualifier. The default value is <none>. Change it to ' and it should work.
    • Proposed as answer by TheVenkster Monday, January 21, 2013 6:26 PM
    Sunday, January 13, 2013 12:48 PM
  • Binho:

    Awesome. I just noticed that the Text Qualifier had a wierd value. I set to to <none> and it worked fine.

    Thank you very much.

    Venki

    • Proposed as answer by c ws Thursday, February 07, 2013 7:17 PM
    • Unproposed as answer by c ws Thursday, February 07, 2013 7:18 PM
    • Proposed as answer by BinzT Tuesday, August 27, 2013 10:45 AM
    Monday, January 21, 2013 6:28 PM
  • I found an answer at http://dba.stackexchange.com/questions/6132/ssis-flat-file-manager-skips-the-last-record and it works great for me.  In BIDS, it was to edit the flat file data source in the advanced tab and set the text qualification drop down to false for all columns.  Package now runs and imports all rows (SQL 2008 R2 X-64).
    Thursday, February 07, 2013 7:23 PM
  • This worked for me.. perfect!

    Tuesday, August 27, 2013 10:46 AM
  • Hi Todd,

    I am facing a similar issue, I have a package residing on server and I am trying to run it using a batch file from Client machine.

    It skips the last record from CSV, is there any solution or workaround for this one?

    Please help, Thanks in Advance.

    Thanks,

    Mihir Shah.

    Monday, March 31, 2014 9:01 AM