Replace a carriage return in SSIS

• Data9

  SSC-Addicted

  Points: 453

  More actions

  July 23, 2014 at 3:36 pm

  #293691

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 23, 2014 at 3:44 pm

  #1731968

  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

• Data9

  SSC-Addicted

  Points: 453

  More actions

  July 24, 2014 at 6:56 am

  #1732085

  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

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  July 24, 2014 at 7:06 am

  #1732091

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

  Can you post the flat file as an attachment.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 24, 2014 at 8:30 am

  #1732109

  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