SSIS Package Help with , delimiter source file

SSIS and Import/Export
1

Hi Guys,

Need Help in SSIS Package to Pull the data from .CSV and load into SQL DB.

Here is sample data looks like.

ADV ,A_DAT ,A_TIM ,P_tit ,S_Tit
"SOCLEAN INC","5/16/2018","14:39:44",""Z-QUNICY,"DR K 60 V1"
"SOCLEAN INC","5/16/2018","14:39:44","Z,QUNICY ,"DR K 60 V1"

The .csv file is (,) delimiter and they put " quote to accommodate, in the data. However, In Some data, they are Putting "" double quotes and it is failing
my package. In "Flat File Connection" Manager I can use ( Text Qualifier = " ). Is it possible I can use both Text Qualifier (, and ") to resolve this type of
issue or any other suggestion? I know, someone will suggest, I will ask the client to fix the data or change the file to "|" delimiter but it is not easy to accomplish.

Thanks for your help in advance.

2

Set the Text Qualifier to non and load into a staging table. Use the Replace function to remove the quotes. Or you can do the same in a Derived Column transform.