First of all, I suggest you subscribe to Relic Developers Network (RDN). And go to their HW2 forum (Tanis Shipyards)
here.
Something to note, the people who made Homeworld also made Dawn of War. It even uses the same file system as HW2!
ESSENTIAL MODS:
Point Defense Systems (PDS) - Adds more weapons to every ship. If you play HW2, you might be wondering "Why does a ship the size of a the L.A. city limits have only 2 weapons?" PDS attemps to fix that by giving the larger vessels massive numbers of turrets. A single battlecruiser can simultaneously engage a dozen or more ships with this mod. Unfortunately, it also adds a couple amateurish ships to the game and voiceovers that are quite poorly made and unecessarily long in duration. Still a good mod though.
HW2 Complex - Similar to PDS in purpose but very different take. Adds new units, weapons and research. Also adds SHIP EXPLOSION DAMAGE AND KAMIKAZI. I personally love this feature. Seeing as how when capital ships are destroyed, they explode violently enough to white out the entire screen, I always thought it was odd that the explosion didnt damage nearby ships. This mod fixes that and also lets you order capital ships to ram other vessels. It is quite possible to take a battlecruiser, hyperspace jump into the middle of an enemy mining operation, ram and destroy their shipyard. Cool.
Star Wars Mod - Self explanitory. I am not sure if this mod is still under development, but in the last release it added about 6 Star Wars themed ships to either faction. The models were not optimized and the super star destroyer was longer than half the skirmish maps in the game, causing gameplay issues. Worth a try.
HW2 Enhanced Gameplay - Absolutely essential! Adds new gametypes for you to choose from, lets you select the music you want to play on the map, starting ships, starting research, resources and specialized players. The latter lets you allow certain players to specialize in certain roles in a team game. For example, one person can be dedicated to harvesting RU's and can spend their entire unit cap on harvester ships and their resources are automatically shared with other team members. Another player can be dedicated to producing only fighters, or only frigates or only probes and scouts. This mod is setup so that you can easily make changes to the ini files to tweak the categories, gametypes, etc to your tastes. Also works in multi.
Capture the megalith mod - This mod essentially enables capture the flag in HW2. A megalith (a chunk of a progenitor ship) lies in the center of the map. Players must send resource harvesters to clamp on and tow it back to their mothership while fending off enemy ships. The first player to capture the megalith wins.
Battlestar Galactica Mod - This is a conversion for HW2 that lets you play as BSG! Im very interested in this mod. I havent tried it yet, but it looks interesting and is in a mature development stage. Give it a try.
OTHER MODS:
Making the Keeper a playable race - Go here to check out a tutorial on making your own mod to make the Keepers playable.
FXMOD 1.04 - This mod adds 2 unused ships back to the game, a Kadesh cruiser and the Progenitors. I dont know what the status is on this mod, but it seems rather poorly made and supported. You also have to download an english language file since its a chinese mod. Not the best mod, but it does what it says.
Kadesh mod - A more serious attempt at adding the Kadesh from HW1 to HW2. This mod is more complete than some other Kadesh mods, but is now inactive. Its the best mod for you if you want HW1 ships in HW2.
Those are some of the best and best supported mods for HW2. A couple of them are inactive and out of date (the Kadesh and Progenitor ones are usually old and unsupported) but still worth trying. Ive checked the threads for the past year and it seems the admins went about locking old threads. Ive done all the searching I care to do and posted my results above. What you see here are the best candidates for the Progenitor mod that are available now.
Hope this helps.