I have a Microsoft SQL Server 2008 R2 SSIS package that was finally decommissioned (I know..).
I migrated the package using VS2019, (destination is a SQL Server 2019). However, I started getting the above error on a a flat file CSV import. The flat file settings include text qualifier double quotes(") and column delimiter comma (,). Interestingly, the flatfile preview looks perfect, even when rows skipped to show the row inducing the error. Yet when the task runs it fails. When I checked the data, a csv row has a set of double quotes within a column text. e.g. 123, "Paul "Jay"", "Jones"
Seems the source writer didn't consider/handle text quotes (escape/remove/convert to single quote). I don't have control of csv source, so can't get it corrected. And its re-generated on a schedule.
Also seems that 2008 R2 SSIS had a more robust/flexible way to process columns in a 'greedy' fashion. Allowing content to be included in a column right up to text qualifier end AND row delimiter. I suppose the same way as the data preview still does.
Investigated solutions but nothing short of having a pre script parsing this very large file, hunting for mid column double quotes. Which I'd like to avoid.
Any creative ideas? Any different way to migrate the original package? Is there a SSIS setting, that would allow the previous behaviour?
Thanks