Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.