The absolute upper-limit is 253 total mods. This only counts certain types of mods, though; specifically, ones that add a .esm or .esp file so they can change actual data in-game (i.e. not mods that simply change models or textures). This also does include the expansions, and any such mods downloaded through Steamworks rather than Nexus.
The reason for the limit is that Skyrim (and other Bethsoft games) uses an internal code (the Form IDs) for everything added by a mod. The first two hex digits indicate where this thing came from, with 00 being the base game, 01 being official patches to the base game, and FF being reserved for items created by player interaction (custom potions, items dropped in-world, summoned NPC refIDs, etc). This leaves only 253 prefixes for other mods, before the game simply cannot load up items from the mod.
Note, of course, that any given collection of 253 mods is likely to be impossible to run together anyway for reasons of mod conflicts or of your computer simply being unable to handle them all. 253 is just the absolute theoretical upper limit.