SQL Server Performance

Flat File Validation

Discussion in 'SQL Server 2005 Integration Services' started by rohit2900, Jan 12, 2009.

  1. rohit2900 Member

    Hi,

    We are having a simple package which will read a deliminated flat file and put in to a database table.

    Now I'm having few concerns on this.

    1.Can we validate the structure of the flat file for invalid record, byvalidating I mean if my flat file is having two fields 1st int and 2ndstring like below

    1,abc
    2,bcd
    3,cde
    .......

    In the same format lets suppose one of the records in between id like

    5
    6, rtz
    .......

    In my package this scenario is failing which is correct but if my flat file looks like....

    1,abc
    2,bcd
    3,cde
    5,def
    6

    Thanmy package is not failing and rejecting that last invalid records withwarning "[Flat File Source - Role Data [1]] Warning: There is a partialrow at the end of the file. "

    i.e. if the invalid record is inbetween then the package is failing with some flat file validationerrors but if it's at the end of the file it's not failing.

    Anybody having any idea.
  2. Adriaan New Member

    Read it as a file with a single text column, and parse the string for further processing.
  3. Harnath New Member

    Hi Rohit,
    As per my knowledge for the first scenario, the package should not fail. Instead it searches for the delimiter in the next line, so once it identifies delimiter ',' from the row 6,rtz it delimits the string there and it treats the data as shown
    5 (Tab character) 6 , rtz
    so it looks like 5 6 , rtz
    For the second scenario it does the samething but it fails to identify the delimiter ',' from that row, so it treats as invalid row and throws you the warning.
  4. rohit2900 Member

    Hello Harnath,
    It is failing with the first step (Flat file source) as red in data flow task for the first scenario
    And not failing for second dcenario.
    Is there any way in the package to validate the record except writing a procedure.
    Rohit
  5. Harnath New Member

    Rohit,
    Let me know the error it is throwing and kindly paste the flat file data if possible.
    For the first scenario i have tried, it worked for me..

Share This Page