How to Redirect Invalid Rows From Flat File with line number and error column name

  • Hi,

    In my package source is flat file, now I want to redirect bad rows to error output with the line number.

    suppose below is my data

    ID,NAME,AGE

    1,AA,30

    2,BB,25

    3,CC,30

    4,DD,a

    Column data type :- ID=Int , NAME=Varchar(100) and AGE=Int data type.

    as you can see that 4th row have incorrect data , having char in int column data type.

    I am able to redirect the bad data to error output , but unable to get the line number and error column name in error output file.

    Kindly help me to achieve above scenario.

  • To get the column name is a bit fiddly but possible (unless you have a time machine and are using SQL 2016 where it's been made simple finally[/url]. Also, who wins the rugby world cup?) and is explained here:

    https://bennyaustin.wordpress.com/2011/02/04/ssis-name-of-errorcolumn/

    As far as a row number goes, I suppose one way to do it would be to pre-process your flat file (before it hits the DFT) to add a row number with a script task and then output this along with the rest of the error columns.


    I'm on LinkedIn

  • Ok,

    Can I get the error line number in my error output file.

    Regards,

    Vipin

  • As I said, to include the line (or row) number you would have to pre-prepare your flat file before it hits the DFT.


    I'm on LinkedIn

  • PB_BI (10/14/2015)


    To get the column name is a bit fiddly but possible (unless you have a time machine and are using SQL 2016 where it's been made simple finally[/url]. Also, who wins the rugby world cup?) and is explained here:

    https://bennyaustin.wordpress.com/2011/02/04/ssis-name-of-errorcolumn/

    As far as a row number goes, I suppose one way to do it would be to pre-process your flat file (before it hits the DFT) to add a row number with a script task and then output this along with the rest of the error columns.

    Thanks for the link.


Viewing 5 posts - 1 through 5 (of 5 total)

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