Is there a way for SSIS to overlook embedded double quotes in the data so when I run my package and the "for each loop" gets executed and imports all the files in a folder into a table the columns wont shift because of the embedded double quotes?
I've read about the Undouble add-on that you download from the microsoft website but I have not had any luck with the installation so I couldn't test to see if it would work. I also tried using OPENROWSET command but anything after the column with a double quote would be NULL.
So basically, here's an example from the csv I'm working with:
1,"John "The Man, II" Doe","Gotham",30,"Villian"
There are no double quotes around 1 and 30 because those are numeric fields when they were extracted into a csv file. The city, age, and title are shift one or two columns over and the name would only be "John".
Does anyone have any ideas on a work around for this?