I am somewhat biassed because I make CC and post it on MTS, and I enjoy the process of making it almost as much as the process of using it. I appreciate that it gives the opportunity to customize the game even further than you can do it with CASt and the other EA tools. But as for whether or not you should use it, well, that's a matter of what you want out of it.
I think that CC which is either creatively made or which serves some real purpose in your game can add a lot of functionality and enjoyment. Things like the "One More Slot Package" or the rabbit hole rugs, or even some of the simple CASTable paintings and carpets that I do, can give you a lot of new possibilities in very few packages. And if you are thoughtful about which CC you want to use, you can keep your CC to a minimum but still enjoy what it has to offer.
In my opinion, what you don't want to do is fall into the trap of thinking "ooh! new hair!" or "ooh! shiny object!" for every piece of CC you find. It's very easy to start wanting all of the free, amazing stuff on a site like MTS, but honestly, I don't even have all of my own stuff loaded, because it just gets to be too much after a while.
So if I were you I would think about what you want out of CC -- Let's say your character is an elf, and you spot the elven build set that is out there. You would probably get a ton of use out of it, have a lot of fun building with it, and generally think it made your game a lot more realistic (well, in a LOTR fantasy kind of way) than without it. But let's say your character is a singer, and you find 325 different dresses and hairs that would look good on stage. You can bloat up your CC folder with all the dresses and hairs, but how often are you really going to use each one of them, and how much do they really add to the game? If you do a ton of screenshots for stories, maybe a pose player will offer you all kinds of new ways to be creative and/or save your sanity. But maybe you don't really need 50 new cars for your Sim to drive around in.
As others here have said, CC will sometimes be buggy, or break with a patch. (I haven't personally experienced any such bugs, and in my experience, that has more than occasionally been true of the official EA content as well, but your mileage may vary.) Most of the bugs will be harmless and can be fixed by just pulling out the CC object. But especially if you are using newly scripted objects (i.e., objects with new actions) or brand new cc that has not been playtested by thousands of people for months, you have to go into it with eyes open, knowing that you might conceivably screw up a save game with an error somewhere. If you are really thoughtful about what you're choosing, and careful about where you get it, then IMHO it's worth the risk.