Introduction
It's common scenario in ETL while you find presence of delimiter character inside the source records while importing data into flat file. Though the flat file will be exported without any error while reading it back it will create problem as the number of columns will increase for that row.
Without wasting more words I will now show to avoid such situations.
Solution
To avoid such problems you need to add a text qualifier in the Flat file connection manager.
In the following example the source data is present inside SQL Server. Following is the data.
We will use double quotes(") as the text qualifier. So while importing the data we will replace the double quotes with two double quotes. Means
This query is placed in the OLEDB source. Then the records pulled will be exported to a comma delimited Flat file. The text qualifier should be set as double quotes(").
While reading the file also the same connection manager should be used. And following is the data viewer screen shot while reading the data back from the Flat file:
