I'm probably one of the few fans of Sean Waltman that are commenting on this thread (I'm sensing most of the replies to OP will be people bashing him relentlessly) and you know sometimes I wonder this very question to myself. He is the sole member of the Kliq who doesn't appear to be a 1st ballot HOF'er (or even a HOF'er at all), and the sole member who really can't stake a claim to being one of the most influential talents of 90's wrestling.
I think his wrestling talent is inarguable, he was very gifted wrestler technically while also combining pure striking with an above average aerial arsenal to boot. While he's not Ric Flair on the mic, I think he gets too much heat for his mic work. He was average, he could absolutely hold his own but I will admit he has only cut one memorable promo in his career (put that in your pipe and smoke it).
As for his decline in 2000, I think all of you hit the nails on their heads. It definitely wasn't one issue but the sum of several factors that lead to his decline and his eventual dismissal in 2002.
1. Lack of creative direction post DX - I think this is the biggest thing, and literally every DX member except Chyna (and obviously HHH) suffered from this issue in the immediate aftermath of DX. For anyone who wants to argue Billy Gunn, he would remain irrelevant for a good two years before getting a solid role as Chuck's gay partner and eventual tag team champion. Road Dogg and X-Pac saw both of their careers and gimmicks become severely outdated until becoming addicted to drugs behind the scenes and losing control. Road Dogg's entrance doesn't make sense if Degeneration X isn't proudly bringing it's tag team champions of the world, and K-Kwik was stupid as hell. X-Pac IMO was kinda the heart and soul of DX, he was actually the guy who used the now-signature DX ring pyro during every entrance and fought for his stable's honor against the Corporation or whatever foes they had at the time. Without that, what was he fighting for? Why was he still wearing the exact same tights? Why was he still using the same DX-related theme song?
2. Drug use - This is Sean's own reasoning for his decline so I hold it in high regard. Obviously becoming a meth head isn't going to help your career regardless what it is, and Sean's heavy usage definitely deteriorated his ability to perform in every facet of the wrestling business and be a good employee. Every Kliq member had quite the ego, that obviously rubbed off on Waltman and I imagine a drugged up Waltman was not the easiest person to deal with both behind and in front of the camera. Chyna saw her career slip in 2001 for the same reason (obviously Steph would be the #1 reason on her list but I'd attribute #2 for drug use).
3. Y2J, The Radicals, Kurt Angle, and the influx of future WWE HOF'ers - I honestly never even thought of this viewpoint until reading the OP's post, but boy does it make damn good sense. How the hell was he supposed to climb the ladder when that ladder was being climbed by the hungry HOF talents of Chris Jericho, Chris Benoit, and Kurt Angle!? Maybe that was WWE's excuse for keeping his gimmick the same, they could only develop so many talents at a time. Regardless, it is clear that those guys had both better looks and complete packages compared to X-Pac. When they temporarily brought DX back in 2000 he actually got to face every single one of these guys on RAW, but I'm pretty sure he won a total of 0 matches against those guys. Hard to maintain relevance when you're the guy who has to put over all the new guys.
I think a major criticism against him that I completely disagree with is that he was only good when paired onscreen with the Kliq. I will admit every time he was massively over with the fans was when he was involved in a Kliq-related storyline but that simply happened to be for the entirety of 1995-1999. Regardless of who he'd been paired with, as long as they would continue to develop his character (which happened successfully during those years) he'd be over with the fans. Maybe you can argue the Kliq took care of him backstage, and when there was no one else helping him fight his battles in 2000 he slipped down the card. For the record, while I'm not arguing that X-factor was memorable at all, it speaks volumes that they would give him his own faction to lead and he actually spent the majority of that time as a champion, being the man who unified the WWE Light Heavyweight and WCW Cruiserweight Championships. In 2002, the Kliq was clearly helping him fight some battles again as he returned to NWO but as soon as Nash went down and they abandoned that storyline it seemed as if WWE was unwilling to find a storyline for him. So all in all, I would blame this on those 3 reasons above. He was a great talent and talented worker that WWE Creative simply abandoned, drugs had destroyed, and the influx of talent overcame to leave him a broken future endeavor in 2002.