http://visakhm.blogspot.in/2014/06/ssis-tips-handling-embedded-text.html
One of the feedback I got was from a ETL developer who asked whether its possible to handle files with inconsistent text qualifiers using flat file source in SSIS. He had also sent a sample file which I’ve adapted here for illustrating the solution I had provided for him.
The file looks like below
As you see the file has inconsistent text qualifier ie for 3rd column it has combination “| as text qualifier and for other columns its | alone. It also has ” characters coming inside data as well (see row 3).
In order handle this the basic approach would be same as what we discussed previously. We will add a separate data flow task to handle the inconsistent text qualifiers and make them consistent throughout.
The first data flow looks like below
The flat file source here reads the entire row contents of the file as a single column. This is achieved by specifying the row delimiter as {CR}{LF} and no column delimiter specified. The derived column task will make text qualifier consistent by doing pattern replace using REPLACE function. The expression used would be this
REPLACE(REPLACE(DataColumn,”,”|”,”,|”),”|”,”,”|,”)
This is then saved into a new flat file temporarily which would have structure as below
