Great, so you just copy pasted a response from Celko. Now I certainly don't...

ksatpute123 (11/18/2014)

---

Disadvantages of money datatype:

It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. Remember the early versions...

Care to explain why money isn't precise?

It would be nice if you would state all of your requirements in your initial question, instead of changing them halfway.

The simplest solution would be to replace '(Part of) '...

ps: Tennessee, Kansas, Ohio and SouthDakota are not countries, but states.

You could replace all of the special characters with one common special character, for example |.

Then you could split the data using a splitter function[/url] and get the first term.

Power View works pretty fine as well.

rhythmk (11/18/2014)

---

Not yet Koen.Actually this is PROD server and not maintained...

Did you try to do a repair of the SSIS installation?

Or maybe remove it and install it again.

OK, great you solved your issue and thanks for posting back.

Ditch the aggregate. You are returning two columns in the subquery, while you can have only one in an IN clause.

Which OLE DB provider are you using in the source?

Can you post the query?

I guess you want to split your data based on the comma?

If yes, read the following article:

Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

ps: "I want to help" means...

Interesting question, thanks.