Escaping Double Quotes in Flat Files: SSIS is different to DTS
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.
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.
aaah finaly someone is sharing my SSIS problem
"blatekst"","blatekst2","blatekst3" fails in SSIS..
even using the microsoft sample 'undouble' (http://www.microsoft.com/downloads/thankyou.aspx?familyId=b51463e9-2907-4b82-a353-e15016486e1d&displayLang=en) does not work; it hangs first on reading the csv before the " is changed...
Thanks,
Jeroen Schoenmakers
Reply to this
Update:
I've discovered that SSIS works differently then the old DTS; The old DTS does some on-the-fly data-changing, fixing several things like a " in a text-field or truncating data if the data types in the db are to short. SSIS does non of that. So i've written my own csv import tool in .net
Kind regards!
Jeroen Schoenmakers
Reply to this