Good Particle effect plugin?

The procedure to using 2D footage as source particles is identical to most, if not all, particle plugins. I'm not certain what issue(s) you faced. Perhaps if you had provided screengrabs and descriptions of issues, someone here could have helped you out. Anyhoo, Red Giant's Trapcode Particular is a great particle system - the only issue I have is with the scubscription payment scheme. 

If using the stand-alone version of BorisFX Particle Illusion, you'll have to render your particle animation and then import that into After Effects.

Per the user documentation, "ProRes is the preferred format, as it’s the only way to embed an alpha channel into the video — select one of the ProRes Presets that contain alpha. (With MP4 you must render the RGB and Alpha as two separate files.)"  (https://borisfx.com/documentation/continuum/bcc-particle-illusion/)

Another option to consider that I don't think has been mentioned yet is stock footage of dust.  It's pretty easy to find clips that are free (https://www.videezy.com/free-video/dust) as well as those that have a fee and then layer that in with a Blending Mode or Magic Bullet Universe Unmult (https://support.maxon.net/hc/en-us/articles/360010345194-Where-can-I-get-Unmult-).  That effect knocks out black.  Unmilt is part of one of their subscriptions, but you could try the free version that was maintained from 2014 to 2017.