_If_ the device supports USB host mode, then you'll need either a very low power device like a plain pen drive and a Micro-USB Male --> USB Female adaptor. Once plugged in the device should just "see" it. And it'll pop up as a separate media device in the filesystem, you'll probably need a filemanager to make much use of it to copy files back & forth.
Anything that requires more than about 50mA to power will need to be plugged in via a USB hub. (USB HDD, keyboard, mouse etc)
Interestingly I found the Transformer Prime able to power portable USB HDDs where other Android's failed, also found Samsung's USB host support quite patchy in comparison to ASUS, so it's a little difficult to know which things will and won't work. Also worth pointing out that most drives will need to be formatted Fat32 to work, again the Prime excels here as ASUS added support for NTFS and a few other formatting standards. I've no recent experience of the original Transformer on ICS, but I expect it's support will be similar to the Prime's (of those tablets the OP mentions I have no qualms in recommending the Transformer as the best).
Don't forget the other things that can work with USB host though! USB keyboards/mice, Logitech unifying wireless USB gear, PS3 controllers, other USB controllers (I've used Logitech's ones). For wider USB device support outside of drives and HDDs the Samsung's are decidedly less compatible than ASUS gear (their bluetooth device support is also worse).
EDIT: Worth mentioning that for Samsung and ASUS gear you'll need an adapter to use these devices with the tablet, PB Tech has them in stock for both brands. If you get the keyboard dock for the Transformer then you won't need the adapter as it has full-sized USB ports.
EDIT: Should mention that my youtube channel has demo's for this stuff with the Prime and Galaxy Note (would post a link but at work, nil youtube access).