Mostly just a simple architectural tool for me.
The actual game play is an improvement over Sims 1. Sims have more familial ties, and go through stages of life. You can literally spawn a whole community with a single starting family (but soon you'll run out of mates as the default townies get married off, and as far as I know, distant cousins can't form romantic relationships). But still, like it's predecessor, it's a whole lot of watching simulated people walking around and living their lives after you've done the building and preparation. Only major choices require the player to step in and make a decision for the Sim. After you've seen them go through certain actions once, it becomes repetitive.
As I said, I have no expansions for Sims 2, so I don't know how this game plays beyond the base game. But as I have all the expansions for Sims 1, I can tell that that expansions don't make a somewhat repetitive game into a more interesting one. It just expands what your sims can do. Watching the expanded actions gets old just as fast.
I do tend to like Will Wright's open-ended SimCity games, but I guess the difference here is those games are more based on building and don't *require* that you watch most of the time. If you stop watching in the Sims, you usually run into a prompt or disaster that stops the simulation. So continual monitoring is almost necessary.