+972 52-548-6969
SSIS vs. CSV
time to read 1 min | 94 words
Another team have run into this bug, what this basically means is that SSIS is not capable of importing CSV files. The "spec" for CSV files is about three lines long, including the special cases ( of which there are two ) and it hasn't changed in a decade.
It boggles the mind that not only isn't SSIS capable of doing this, but they are not going to fix it for SSIS 2008. Looks like you would need to wait to SSIS 201x for a version that can handle CSV files.
Comments
had the same problem some time ago. i solved it with a custom source script component.
Ayende, I feel your pain... a couple years ago, I tried to use SSIS to build an export component for a project I was working on, as I assumed it would do a better, faster job of creating CSV files than I could... WRONG! To my utter disgust, the CSV formatter doesn't escape quotes for you, meaning you'd have to write some custom code to transform your outgoing data to escape the quotes properly. At that point, I said, "screw it, I can do this easier and more flexibly by just looping through a stupid dataset." Add that little thing to the fact that in order to do a dynamic export (where the user would choose which data fields to export), you basically have to programmatically create a dtsx package on the fly, and it pretty much becomes useless in an enterprise production environment.
Unfortunately CSV format isn't even standardized among Microsoft products. I've had several occasions where I tried to use CSV files created by Excel and tried to use them with other MS products and hit failure. Usually it's due to the "special cases" like double quotes, etc., but you're right, it's inexcusable. In the meantime, I tell my developers to use a safer format like tab-delimited to avoid the issue altogether.
Hey Mike, stupid question here... how are tab characters escaped in tab-delimited formats?
Comment preview