April 12, 2007 at 3:04 pm
Each morning I have to download a .csv file from a website and imort it into SQL 2005. I've written a simple SSIS package to handle this. The problem is that the original data comes (from a 3rd party) from information entered via a web form. Often, the person filling in the form will enter quotes ("), line feeds, carriage returns etc in the comments field. These cause the import to fail. How do I, as part of the SSIS package remove these characters from the file? It's not too bad at the moment because there is only one file so I manually correct these entries before processing the file, but the volume is about to increase 100 fold...the package will need to poll for a new file every few minutes and process it quickly. There's no way I will be able to keep up with that. Any help is appreciated. I've scanned the posts here but haven't been able to find the answer (maybe I'm just searching incorrectly) so I apologize if this has been answered elsewhere.
April 12, 2007 at 5:33 pm
There's probably a way to do this with native MS technology, but the way I did this in my last life was to download one of the freeware sets of Unix tools for Windows, write a quick SED script that striped out the offending characters and then kick off the import. Easy to do , easy to modify.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply