Problem with Importing flat files with embedded (CR)(LF) characters.

  • Could someone assist I have a package which imports data from a flat file the connection manager has been set with a (CR)(LF) as its Row delimiter. There is a column called Comments, the last column to the right which has its text in quotes (text qualifier "). However there are times when the comments include embedded (CR)(LF) characters where the end user of the app hits the return key to skip to a new line, because the row delimiter is set as a (CR)(LF) the Flat file connection manager in SSIS interprets this (CR)(LF) as a new row and the import fails.

    This cannot be a unique problem to me can anyone provide a solution. I was trying to use the Derived Column task to add a append the comments column with a pipe character and change the row delimiter to pipe (|) however I have had no luck with that.

    I have no control of hoe the flat file is formatted its a csv with test column that can have (CR)(LF) whenever a user leaves a comment and hits the return key.

    Good suggestions that already worked are welcomed.

  • Hi

    Did you try to use Script Transformation ?

    It is more flexible and you can read the file line by line and do the checking before exporting it to another component

    :w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

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

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