Tuesday 1 October 2013

How to handle source records containing delimiters in delimted Flat file connection manager

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: