March 14, 2011 at 3:25 pm
Hi,
I have a SSIS package that imports csv file into a SQL server table.
It gets imported fine as long as the the column value itslef doesn't contain double quote in it. For e.g there is a description column and it can contain any comments like - "they booked a "FLIGHT" in Southwest". When it encounters this type of records, then everything gets jacked up. Meaning one column value goes to other column and other column's value goes to the next column.
Anyway to get around this? I hope I made my question clear.
Thanks!
March 14, 2011 at 5:31 pm
I am not having the same issue. Do you have a Text Qualifier set on your Flat File Connection?
Please post the line of the file having the issue, the properties of your Flat File connection and the exact build of SSIS Designer you're using (In BIDS go to Help > About and then Highlight Sql Server Integration Services and look at Product Details).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 14, 2011 at 6:24 pm
barunpathak (3/14/2011)
"they booked a "FLIGHT" in Southwest"
If they're not escaping characters like this on the load, you have to use something other then a quoted identifier for text entries. Try a pipe, but you'll have to get the csv file modified during it's build to use this instead.
There is no direct fix inside SSIS. You'd have to load the raw data, find and escape out the quotes (searching for ," or ", can usually work) swap them for your new delimeter, save it as a new csv, and then load the new csv with the workaround.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 14, 2011 at 8:47 pm
My Flat File Connection is setup with a double-quote as a text qualifier and I did not have any issues importing a file containing the data shown...
I see this issue cropping up a lot in the forums and reports vary as to the behavior with respect to importing dlimited flat files with embedded delimiters and qualifiers. Please post which version of SSIS you're using so we may get to the bottom of the issue. To get the client designer version see my previous post. To get the server version see this article: http://support.microsoft.com/kb/942177
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply