Having now completed Shivering Isles, I have some mixed feelings on it. The setting is fantastic, to start with. The theme of duality that's always present is a clever little feature, like with Mania and Dementia, Bliss and Crucible, the Golden Saints and Dark Seducers, etc. The new creatures and plants are creative, the eccentric characters are a lot of fun, and Sheogorath himself is hilarious, as he always is.
What drags the add-on down, however, is the main quest. It really, really sucks. It makes very little use of the excellent overworld, instead sending you on a series of very long, very tedious dungeon crawls, almost always to do something that feels mundane and unfulfilling, like fetch a MacGuffin or flip some magical switch. Exactly how these boring quests actually relate to the eventual goal of stopping the Greymarch are handwaved away with vague explanations, which hurts player motivation and the feeling that you're doing worthwhile things and making real progress. There are a few decent quests there, I'll grant, like the one where you have to either kill some unwelcome adventurers or else drive them insane, but for the most part, they're just busy work. Huge disappointment.