Well, of course. I can install 400 mods in a game and there isn't necessarily anything stopping me, but it'll probably cause dozens of major conflicts, increase load times to an unbearable point, or melt my computer.
However, if we disregard technical issues and consider only the gameplay aspect and how much until modding stops enhancing the game, it's something else entirely. A real problem with a lot of mods is that content creators often have no idea of being subtle or making things work smoothly. In other words, a huge number of mods out there are overdone. Last month I installed a home mod for Fallout: New Vegas, and all I really wanted was a spacious interior with all the amenities I needed and a comfortable design, something which seemed like it could fit in the vanilla world. Instead I got a gigantic vault with a dozen bedrooms, a massive laboratory with tools nobody ever uses, storage rooms that are nothing but ugly lines of containers with unecessary auto-sorters, large armories and even a VR pod and a teleporter which seemed totally out of place. It could have been a nice mod, but the author just didn't know when to stop adding things. In addition, I think a lot of developers have trouble recognizing feedback. See Skyrim - when it came out, everyone loved all the armor mannequins and weapon displays, and people wanted more, so the logical idea was for modders to make houses with more mannequins and displays, right? The problem is when people add too much. I once downloaded a mod for Skyrim which was a tower, in which an entire floor was dedicated solely to a circle of mannequins and nothing else; the floor right below that was just dozens of displays. It's unrealistic, awful design, when good planning would instead result in placing those elements in a way that worked harmoniously with the rest of the location. All because they responded to player demand excessively.
It's all about balance of design. And it's not just about a mod working with itself and with the vanilla world - what happens when you have 50 major mods and you want them to not conflict thematically? Because even if it's all technically compatible, not everything may fit in the universe. Personally, I'm actually a big fan of altering games in large ways. I always play every game 100% vanilla at least once, but after that when I'm revisiting what I already played, it can be very interesting to overhaul everything and get a whole new experience, dig? So let's build new towns, spawn new characters, complete new quests, and install as many graphical enhancers as my hardware can handle. It's about making it more enjoyable and fresh even if the original experience was already good. And that's exactly where the problem is.
How many content packs can I add until it stops working seamlessly? Nobody is a fan of a particular mod sticking out like a sore thumb, any player wants them to improve the game as a whole. But no, I do not believe this has anything to do with "ethics" and taking the game too far from what the creators intended. That would only happen if you modded your game extensively and then judged the original work based on that. In this case, you've already experienced the game as it was initially built, you may or may not have enjoyed it, but you've seen it. Afterwards, what you do with the software is about how you, and you alone, want to change the experience into something better or simply newer. I talked about having everything work harmoniously. The reality is, that depends entirely on your standards and what you put into your game. If you're not bothered by having various levels of different design philosophies coexisting, then you can mod as much as you like. Alternatively, if you don't like stuff that feels out of place within the lore, that's the limit of which mods you can use. And if you can handle lore-unfriendly mods so long as they all add up to make a great experience, that's the limit you're setting for yourself before it's too much. The only factor is taste and entertainment value.
