I am building an SSIS package in SQL Server 2008. One part of it is Data Flow Task that has a flat file being imported into a table in my database. I go into the task and create a Flat File Source. I point it to a txt file provided by another department. The file has fields seperated by commas and the data inside of double quotes for each peice, but it is not a csv file type. So I set the format as delimited, text qualifier as double quote, and column delimiter as comma. My problem is that in one of the fields they have a comma in the data which the connector is making into a new field. Here is an example:
"LastName","FirstName","Clerk II","Health, Physical Educ & Leisure Services"....
This is being imported as
Physical Educ & Leisure Services (instead of Health, Physical Educ & Leisure Serivces)
How can I import that correctly?