I need to use SSIS to load over 1000 csv files into a table, but the csv files are problematic. Only the description field has double qotation mark. Other fields do not. Inside the description text there is either 1) another double quotation mark for inches; or 2) a comma to separate words.
example for case 1):
"Regular Rate, studio suite (1 king bed, 1 sofa bed), microwave, mini-fridge, 30""tv, stereo/cd, coffee maker, delicious breakfast"
In this case if I put " as the text qualifier the description will be cut and not imported as a whole complete field.
and example for case 2):
"CORPORATE RATE, STUDIO, 1 KING BED AND 1 SOFABED, FREE BREAKFASTFREE WIRELESS, FREE PARKING"
in this case if I do not put " as the text qualifier the comma would cause a problem by messing up the delimiters.
So I guess the only way out is to use scripting - do this in script task. Has anybody done this before and have an example or is there any other way around? Thanks!