Well the most popular mods should be free downloads on XBL/PSN, and anything else would be nice to copy over on flash drives. I mean really, how hard can it be, we can already transfer content from storage devices so the structure should already be there.
Harder than you'd think actually.
Because the mods for games like Skyrim are intergrated into the main world and not a standalone map or whatever (Like Halo or UT3), there's a greater risk of something messing up.
Because of that potential for things going wrong, Microsoft and Sony won't pass off on modding a game like Skyrim on the consoles. The only way they'd allow it currently is if mods are screened before being made availiable for download. ie: they are checked for content, that they won't overtax the hardware (Remember both consoles pack a lot less ram than the averge PC), they they aren't so buggy as to cause problems and that they don't require third party tools.
Bethesda, Ms or Sony are the only ones that would be permitted to screen them and MS would definitely charge for the privilige of having done so.