I use template metaprogramming all the time, but in D, not C++. C++'s template metalanguage was originally designed for simple type parametrization and became a Turing complete metalanguage almost by accident. It is therefore a Turing tarpit that only Andrei Alexandrescu, not mere mortals, can use.
D's template sublangauge, on the other hand, was actually designed for metaprogramming beyond simple type parametrization. Andrei Alexandrescu seems to love it, but other people can actually understand his D templates. It's also powerful enough that someone wrote a compile-time raytracer in it as a proof of concept.
I guess the most useful/non-trivial metaprogram I ever wrote in D was a function template that, given a struct type as the template parameter and a list of column header names in an order corresponding to the variable declarations in the struct as a runtime parameter, will read in a CSV file, and return an array of structs, one for each row, with each struct field corresponding to a column. All type conversions (string to float, int, etc.) are done automatically, based on the types of the template fields.
Another good one, which mostly works, but still doesn't handle a few cases properly, is a deep copy function template that handles structs, classes, and arrays properly. It uses only compile time reflection/introspection, so that it can work with structs, which, unlike full-blown classes, have no runtime reflection/introspection capabiities in D because they're supposed to be lightweight.