Spray-in expanding foam insulation?

Alright I'd heard of this but not ever considered it nor had opportunity. However I'm doing a new addition from scratch and spoke to a contractor who does this. It sounds like this will seal the construction up tight and I am told it can even be sprayed in the attic on the roof - that the attic will then be as comfortable as the rest of the house. It's supposed to be left sealed, no vents. There will be Tyvek wrap and styrofoam under the siding too. The windows will be as good as I can afford so I think this will be really quiet and effecient if I do it. I have a small under roof area I may do myself that has zero insulation now but the rest done by a pro if I do it.

Does anyone have any experience with this? Do's/Don'ts? Brands? Advice, comments?

This will be a second story addition, the lower half of the house is a second zone and not yet well sealed. I hope to do that later but since the walls aren't open it will be harder, the crawlspace might be easier once the old crappy batten is removed. The lower half has good windows but poor blown in paper that I'm quite sure has settled over the last 15++ years

Thanks!