I generally think they're more trouble than they're worth. They're certainly far, far more common than they should be. I've had a vampire come in the first two migrant waves without fail in every fort that I've made/done testing in so far, which while being silly is also a pain because they tend to muck up your fort before it gets off the ground because you don't even have a sheriff yet (without resorting to metagame solutions anyway). If vampires are that common there should be some well-established ways of dealing with them; not sure why executing one with a pointy bit of wood to the heart would be a complicated affair, or even exile once they've been found out. Having some more weaknesses should also be on the table for them, like the usual garlic or animals reacting to them strangely, especially dogs. There should also be some skills in the game that have a chance to unmask intelligent creatures that are hiding something, basically judge of intent with more depth. Right now the metagamey solutions feel like cheating, but necessary cheating since there aren't really legitimate ways to deal with them.
I like vampires in concept, fortresses should certainly have some internal threats to worry about, but they're not executed very well at present. Vampires are something that should spring up maybe once or twice in three or four fortresses that make it to 200 population, something that is mostly a threat if you get too complacent about in preparing for it, like most threats in the game (I would support them being much deadlier and liable to cause widescale trouble in this scenario though). The situation as it is now with most forts having vampire mayors, book keepers, eternal lever pullers, and starvation chambers for all migrants being a necessity if you want to keep your important dwarves alive seems silly and makes vampires a far, far more crucial part of the game than they should be.