I've yet to try Tiny Tina's DLC, but the other DLCs have been pretty "meh" in my opinion. Nothing at all approaching the awesomeness of that Halloween-themed zombie romp in BL1.
I loved the Dr. Zed's Island DLC, it was by far my favorite part of BL1. Tiny Tina's comes really close, if not even better. You're essentially playing as a D&D character of one of the original vault hunters, rather than a character yourself.
It has some neat little bits, like if you find a special weapons crate, you get the opportunity to roll 1D20 to determine the quality of the loot inside the chest or you can spend eridium to roll 2D20 to get an even better chance at better loot.
So far, it also seems more balanced. The main BL2 game seemed that the enemies were resistant to almost every single kind of elemental damage. So far, in Tiny Tina's, the enemies will be resistant to one kind but not more than one. Of course, I'm still early on in the game so maybe eventually they'll get stupid resistant as well.
But I will agree with Bonobo that it returns you to the feeling that if you've got the right weapons equipped, you feel like an over powered superstar. While the main BL2 campaign and the other DLC's all really took that feeling of awesomeness away for some reason.
It's also really cool that they didn't just re-name and slighlty re-jigger the normal enemies from the game. In that respect, it also really mimics Dr. Zed's, where you were battling enemies you didn't encounter in other areas of Pandora.
I highly recommend it, it's made BL2 fun again. I'm glad I bought the season package, it was worth it just for this DLC and if I hadn't bought the season package after playing the others, I probably wouldn't have paid for this one and I would have missed out.
