Strip field of commas before inserting into database

Hello.
I have a SSIS process which sorts through csv files and text files. Within my process, the source files are within a loop.  I have stored procedures that cleans up the data once it is inserted into the database.  I have a problem with one of the columns, called “Title”.  I noticed that there are times that this column has multiple commas, which means it is not being parsed correctly.   I believed it was because as my delimiter is a comma, once the comma is recognized, the data is inserted into its appropriate column.  I want all commas stripped from this field.   I thought that I could use a derived column within my data flow task, which would actually be  from the CSV file source to the derived column and then to the multicast. Is there a way that I can remove all commas from this field before it is inserted into the database?   There are thousands of files each day that will be processed.   SSIS Process- Derived Column
ayoZenAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
0
 
ayoZenAuthor Commented:
Thank you Icohan for your comment.  Is the derived column the right route to take or is there a different task I should use to perform this action (before I go to the outside tool component)?
0
 
lcohanDatabase AnalystCommented:
Not mandatory but I would read the file in and use a different task if possible and if derived does not work.
0
 
ayoZenAuthor Commented:
Right.  Ok.  I think that's my issue, which task  prevent the field from being improperly parsed?
0
 
ayoZenAuthor Commented:
Per your suggestion Icohan, I used the Replace in Files tool,http://www.freedownloadscenter.com/Utilities/Text_Search_and_Replace_Tools/Replace_in_Files.html,  which was pretty efficient.  I didn't need to revise my dtsx project either.

Thank You.
0
All Courses

From novice to tech pro — start learning today.