Thanks Phil,

Yes I can see how changing the column delimiter would indeed be the easiest solution. I suppose if Excel, Access and SQL 2000 DTS can handle that, there must be an algorithm that I can use to reformat the data. For now I will probably bite the bullet and spend a day writing format files to use with BCP as I did in the past. The thinking here is that at some point Microsoft will no longer allow backward compatibility with SQL 2000 DTS but will always support direct commands.

The data suppliers are supplying the data under protest because our customers are requesting it to use in the application that we developed for them. The supplier would prefer to have those customers using THEIR interface to the data.

I just wish MS hadn't designed a gun that decides when it's OK to unlock the safety, or that requires 2 or 3 steps to do so. I have a mountain lion about to pounce and tear my throat out. By the time I figure out how to release the safety I will be dead meat. I've never heard of an autopilot system that doesn't allow human override. When that time comes...God save us.

OK, it's time to get back to my world as a software developer that creates business applications to make life easier for people. From Design, to Data, to Implementation, to Testing, to Deployment and finally Support and Feature enhancement. Ah, the life of a Data Specialist must be fun. Diddle with my Data all day long. LOL

Phil, you seem like an OK guy with lots of life experience. The picture you use as an Avatar looks like me as I deal with this situation.

I am thinking there must be an algorithm using Regular Expressions that might handle the few different variations of replacing the delimiting commas in the correct locations. You chose a | as the replacement which may be the best choice. I was thinking along the lines of ~~, which I sometimes use when storing a list of short strings in a table column, which after retrieval are Split into an array for use in the ASP.NET application. Hmmm, I'm wondering if SSIS will even allow me to use that character pair. Probably not. LOL

I will indeed post a solution when I find one.

Thanks