Flat file manager TextQualifier not working

Question

I've a CSV text file which contains

col1,col2,col3

"abc,cdef",a,b

When I use the Flat File Source and preview the a

• bc,cdef appears on first column.

But when i run and view the result in data viewer

• the first column contains "abc and second column cdef"...

I've a textQualifier set to "

can anyone please help. I'm using SSIS 2008 R2

Answer 1

It seems a bug, I have found: SSIS flat file parser does not read Column delimiters embedded in text data. Work around: using some exotic separator.

Answer 2

I have encountered a similar issue, however I found that it was not due to TextQualifier not working, but issues such as 1. Rows with Extra Columns 2. Rows with Too Many Text Delimiters

My approach to identify the errors was to stage the raw data into a table where I could search for possible errors

1. Create an SSIS Package & Load All Data in Row into a Single Column table definition is below
2. In SSIS Package add Row Number ( so you can reference the actual line number in flat file to fix issues http://support.microsoft.com/kb/908460 )
3. Run SQL Agains the rawdata table and count the number of text qualifiers and by subtracting comparing length of string with delimiters replaced.
4. Find any rows where number for text qualifiers or delimiters is not consistent and search for errors

create table rawdata( my_row_number int not null primary key clustered ,rawdata varchar(8000) ,issuspect bit default(0) )

select my_row_number ,rawdata ,len(rawdata) ,len(replace(rawdata,'"','')) as len_without_textqualifiers ,len(replace(rawdata,',','')) as len_without_delimiter from rawdata