Good, but play the next-gen one.
While the basic Double Agent story is the same, the two games were actually developed by two different Ubisoft branches. The games end up having completely different levels and level design, different plot lines and choices to make along the way, and different gameplay mechanics.
The last-gen version (Xbox/PS2) is built on the Chaos Theory engine, and has all the weapons/traps/moves/and indoor spaces that you have come to expect from Splinter Cell. The 360 version has a lot more open spaces, daytime missions, interactive cinematics, and slightly tweaked (but familiar) interfaces to the controls. The 360 version is not without it's minor bugs, but it feels fresher and more exciting, which matches the new direction of the storyline.
The storyline is a big departure from the previous Splinter Cell titles. In both versions of the game, Sam is undercover in a terrorist organization and has to make choices that will gain trust with either the NSA or the terrorists. The Xbox version has a single trust meter, meaning that every choice you make for one side automatically decreases your trust with the other. There are two separate sliders in the 360 version, so the terrorist and NSA are more realistically unaware of your actions for the other side.
The Xbox version of the game gives most of it's story and exposition in cut scenes, while the 360 version has you interact with characters in the terrorist HQ, record conversations, and generally feel sneakier and more involved. More like Sam.