Replace a carriage return in SSIS

  • Hi All, I have a flat file with data as below:

    row delimiter: {CR}{LF}

    column delimiter: | -- Pipe

    Some times the data is being messed up and the pipe is moving to next line. please see below:

    1|test|1234.45|testing|test123|

    2|test|1234.45|testing|test123|

    3|test|1234.45|testing|test123|

    4|test|1234.45|testing|test123

    | -- error here

    5|test|1234.45|testing|test123|

    6|test|1234.45|testing|test123

    |

    7|test|1234.45|testing|test123|

    8|test|1234.45|testing|test123|

    Please suggest me the way I can move the pipe to previous row. I am thinking we should replace "{CR}{LF}|{CR}{LF}" with "|"

    Thanks in adv

  • What about using "|{CR}{LF}" as a column delimiter?

    Then you can remove the additional carriage return on your last column in another step.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis, But only some rows will have the pipe symbol followed by carriage return not all. please see my commnets below

    1|test|1234.45|testing|test123| ---|{CR}{LF} will work

    2|test|1234.45|testing|test123| ---|{CR}{LF} will work

    3|test|1234.45|testing|test123|

    4|test|1234.45|testing|test123 ---|{CR}{LF} will NOT work

    | -- error here

    5|test|1234.45|testing|test123|

    6|test|1234.45|testing|test123

    |

    7|test|1234.45|testing|test123|

    8|test|1234.45|testing|test123|

    Appreciate your responce

  • Are you trying to do a export or import on this flat file.

    Can you post the flat file as an attachment.

  • Data9 (7/24/2014)


    Luis, But only some rows will have the pipe symbol followed by carriage return not all. please see my commnets below

    1|test|1234.45|testing|test123| ---|{CR}{LF} will work

    2|test|1234.45|testing|test123| ---|{CR}{LF} will work

    3|test|1234.45|testing|test123|

    4|test|1234.45|testing|test123 ---|{CR}{LF} will NOT work

    | -- error here

    5|test|1234.45|testing|test123|

    6|test|1234.45|testing|test123

    |

    7|test|1234.45|testing|test123|

    8|test|1234.45|testing|test123|

    Appreciate your responce

    Line 4 will work because it will end in the next row. Your last column will include a carriage return. You can remove it with a derived column. Additional to that, you will get an additional column that you shouldn't include in your flat file source. Other than that, the test I did, ran with no problem.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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