C++'s template metaprogramming was "found" as an accident, and is a very bad example of metaprogramming as a whole. (Techniques like SFINAE and CRTP seem more like a hack, and leaves a bad taste in your mouth when you use it.)
What we should talk about instead: hygienic macros in Scheme/Rust, AST-level modification in Nim, dependent types, etc. I think metaprogramming is an aspect of programming that should be explored more, and is definitely useful for reducing boilerplate.
C++ template metaprogramming may be overused and very difficult to use, but its prevalence despite such difficulty precisely shows how much such a type level metaprogramming tool is desired (compared to syntax level ones like lisp macros or text level ones like C preprocessor and various code generators). New programming languages should try to improve upon C++ template metaprogramming and make more powerful and effective metaprogramming language constructs, instead of completely abandoning the front.
The source of complexity is in the problems C++ is designed to solve. I use C++ metaprogramming for problems that require it.
Sure, Boost is full of metaprogramming insanity, but with today's C++ standard library it's not necessary to use Boost.
10 years ago template heavy code in C++ did suck, because we had slow compilers, dumb build systems, 7200 rpm HDDs and a need to use Boost to augment the bare bones standard library.
I'll be the first to admit that C++ has decades of cruft and design errors. But the metaprogramming works pretty well now. C++'s current problems are (IMO) weird undefined behavior, e.g. the distinction between POD and non-POD types; having both pointers and references, the ancient exception system, and in general a lack of tools for enforcing memory safety.