Flat File not moving all data

  • Hi,

    Im using flat file (.csv) to sql 2008, which has only 40 rows but 39 rows get transfered 1 row missing.

    Also i see and warning sign as

    below

    [Flat File Source [1]] Warning: There is a partial row at the end of the file.

    Anyone have any clue about it.

    Shaun..

  • Open the file in a decent text editor like notepad++ and set it to show all symbols.

    It might be that your last line has an incorrect structure.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hi Koen,

    I had checked the structure looks good..any other way to identify the issue.

    Shaun..

  • I also deleted 5 rows..and it always showing 1 row less..

    Shaun

  • Is the missing row the first, a middle or the last line?

    Is it trying to read one line as a header?

    Is there a carriage return on the last line?

  • Is the missing row the first, a middle or the last line? last Line Missing

    Is it trying to read one line as a header? im using header rows to skip = 0 and checked columun names in first data row.

    Is there a carriage return on the last line? don't know what thise mean??

    Shaun..

  • Shaun2012 (10/2/2013)


    Is there a carriage return on the last line? don't know what thise mean??

    Shaun..

    Is the last line terminated with a carriage return / newline? eg.

    This,is,my,last,line<EOF>

    or

    This,is,my,last,line

    <EOF>

    Note the <EOF> is not the actual characters, just indicating where the file ends

    In your text editor page down to the end of the text file, if the cursor remains on the last line of data then go to the end of the line and press return. It may not stop the warning, but it will mean that you load all the data.

    Assuming that you are using the import data task from SSMS, you will notice that the column delimiter for the last column is likely to be {CR}{LF}

  • no it is on the last line..

  • Can I send u my file..

    Shaun

  • Shaun2012 (10/2/2013)


    Can I send u my file..

    Shaun

    Just add it as an attachment to a post.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • i had sent u an e-mail via private message..

    shaun..

  • bad form. just post it.

    the more eyes on your problem, the faster you get an answer.

  • I've run into this when there is an extra CR/LF at the end of the file: In other words, your last actual line of data has a CR/LF and there is another CR/LF immediately following.

    I've never had to do this, but I've read others have corrected the problem by setting the TextQualified property of the flat file connection manager to false or adding a text qualifier somewhere in the data. See here and here.

    There's also a known bug when using fixed-width flat files although that applies to SSIS 2005.

    ____________
    Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

  • I am getting all the data, in my text qualifier i had "_x003C_none_x003E_" which i removed it but can anyone tell what does do..in the text quliafier

    "_x003C_none_x003E_".

    regards

    Shaun

  • Shaun2012 (10/3/2013)


    I am getting all the data, in my text qualifier i had "_x003C_none_x003E_" which i removed it but can anyone tell what does do..in the text quliafier

    "_x003C_none_x003E_".

    regards

    Shaun

    For some reason SSIS puts _x003C_none_x003E_ instead of <none>.

    More information.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply