Just a heads-up as I noticed today that the import behaviour of escaped double-quotes in a string is different in DTS and SSIS.

If we have the following single entry in a text file:

"xxx""999""xxx"

Using a Transform Data task in DTS, the resulting database entry will be:

xxx"999"xxx

Using a Data Flow task in SSIS we will get:

xxx""999""xxx

In both packages the double quote is defined as a text delimiter and apart from that there don't seem to be any relevant configuration possibilities to finetune the behaviour.

It seems DTS interpretets this as an intentionally escaped double-quote, and removes the extra characters automatically. SSIS on the other hand assumes that this is a valid string entry.

I'm currently converting DTS packages to SSIS at the moment and as the import behaviour has changed in this way, my solution is to run an immediate update (ExecuteSQL task) on the destination data and replace
adjacent double quotes with a single one - not nice. I could alternatively strip them out in the data flow, using a derived column transformation - also not nice. Hopefully this'll be exposed as a property of the Flat File connection manager some time.