I really like this eggcorn. It makes sense: squid live in water and I imagine living in water makes you damp. I’m not sure why damp squid are seen as failures or as disappointing; that seems rather unfair on them.
A damp squib is a situation or event which is less impressive, exciting or popular than expected.
A squib is a type of firework or firecracker. Small explosives used in the military and in industry are also called squibs. If a squib gets damp, it won’t go off.
To British readers – I hope your Bonfire Night wasn’t a damp squib. If you are going to see fireworks this weekend, I hope you have a wonderful time! (Fireworks are one of my favourite things.)