Until the day comes you're ready to repair the sheet metal, (by a shop) properly, and repaint the repaired metal, your goal may simply be to minimize leaking into the interior, and not worsening the problem with exposure to rain. If garaging is an option, this will help. But some hot and humid climates are prone to spontaneous rain storms when out shopping and cruising.
The spray on stuff is an option. Another is the foil-backed butyl tape used to seal HVAC ducts in buildings and sold at most hardware stores. That stuff is super sticky and never hardens and holds up well to the environment and exposure to water.
De-rust the area as best you can, prior to repairing and treating.
Considering the crumbly nature of rusted metal, caulking would not be my first choice. Just like the other options above, you want those rusty crumbles gone, before applying any kind of sealant.
Lastly, as others have suggested, I don't believe this is a common failure, so finding the cause of the rust may rank high on your TODO list.
Observing how the rust seems to follow the curve of the upper cowl, it does not appear to be caused by runoff from the roof or windshield. My guess is the cowl area inside is filling with standing water, and rusting from within. If this is true, that's the leak you want to find and repair.
