ayoZen
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not mandatory but I would read the file in and use a different task if possible and if derived does not work.
ASKER
Right. Ok. I think that's my issue, which task prevent the field from being improperly parsed?
ASKER
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.
Thank You.
ASKER