<I'm heading off to the New England states later this month, and am interested in trying to locate some dinosaur sites in Connecticut and Massachusetts.>
As a lifelong resident of Connecticut, I'd say my two favorite places to go here are Yale's Peabody in New Haven (off I-95) and the dino park in Rocky Hill (off I-91). The Rocky Hill site, state operated, includes footprints made by dinos traveling the muddy shoreline between Canada and New Jersey on their vacation... uh, migration. It was a rainy day, you can see the droplets in the rock, and traffic was heavy as ever.
The park also includes an enjoyable walk over a swamp on a very long wooden bridge, though I've never taken the walk in mosquito season.
The footprints continue outside the building, and you have a chance to do a casting or actually find prints on your own.
Don't allow a whole day for it, but makes for a good few hours. The tracks, 3 toed and said to resemble dilophosaur, are the state 'fossil'.