Spent the afternoon investigating this issue, discussing with colleagues and found out that we'd also had issues installing PGP on a few PC's with a similar error message appearing.
So, to cut a long story short, we attempted the same fix. Looking at the msiexec.exe, msi.dll and msihnd.dll files on a computer that successfully installed site advisor we found that the date stamps on these files were the same (in our case 19/5/2008) and the pc's where site advisor wouldn't install the date stamps were different.
So we copied the three files mentioned above, from the pc that had site advisor installed to the one that wouldn't install, to the c:\windows\system32 folder and to the c:\windows\dllcache folder (msi.dll was in use so this had to be done in windows safe mode command prompt). Once rebooted and booted in the normal windows xp mode, we reregistered both dll's (at command prompt Regsvr32 msi.dll and Regsvr32 msihnd.dll).
Opened the McAfee Agent Monitor and clicked on Enforce Policies, which triggered the Site Advisor Deployment Task... Site Advisor installed fine and is working.
Still don't understand how other McAfee products installed okay and Site Advisor didn't?