Actually, the semisimplicity should hold with no hypotheses on X, so no example should exist. In fact it is generally expected that, with char. 0 coefficients and over a finite field (both hypotheses being necessary), every mixed motive is a direct sum of pure motives -- so the question for arbitrary varieties reduces to that for smooth projective ones.
The reason is as follows: the different weight-pieces have no frobenius eigenvalues in common (by the Weil conjectures), so the weight filtration can be split by a simple matter of linear algebra. (And the splitting will even be motivic since frobenius is a map of varieties.)
Edit: In response to Jim's comment, let me try to provide a clearer argument (2nd edit: no longer using the Tate conjecture). I claim that if we assume the existence of a motivic t-structure over F_q w.r.t. the l-adic realization in the sense of Beilinson's article http://arxiv.org/pdf/1006.1116v2.pdf, then provided that H^i_c(X-bar) is Frobenius-semisimple for smooth projective X, it is in fact so for aribtrary X.
Indeed, given a motivic t-structure, its heart is an artinian abelian category where every irreducible object is a summand of a Tate-twist of an H^i(X) for X smooth an projective, and furthermore there are no extensions between such irreducibles of the same weight (this is all in Beilinson's article).
That's all true over a general field. But now let's argue that, in the case of a finite field, there also can't be extensions between such irreducibles of different weights; then in the motivic category all of our H^i_c(X-bar) of interest will be direct sums of summands of H^i(X)'s, and we'll have successfully made the reduction to the smooth projective case.
So suppose M and N are irreducible motives of distinct weights over F_q, and say E is an extension of M by N. Consider the characteristic polynomials p_M and p_N of Frobenius acting on the l-adic cohomologies of M and N. By Deligne, they have rational coefficients and distinct eigenvalues, so we can solve q * p_N == 1 (mod p_M) for a rational-coefficient polynomial q. But then (q*p_N)(frobenius) acting on E splits the extension (recall from Beilinson's article that the l-adic realization is faithful under our hypothesis), and we're done.
Later commentary: apparently, when I wrote this I was a little too excited about the perspectives offered by motives. I should emphasize the point essentially made by Minhyong Kim, that the reduction from the general case to the proper smooth case likely doesn't require any motivic technology, and should even be independent of any conjectures. One just needs to know that there's a weight filtration on l-adic cohomology of the standard type where the pure pieces are direct sums of direct summands of appropriate cohomology of smooth projective varieties. As Minhyong says, this probably follows from Deligne's original pure --> mixed argument, via use of compactifications and de Jong alterations. Or at least that's what it seems to me without having gone into the details. I'm sure someone else knows better.
Best Answer
I can tell you how they are related.
Before Riemann people would say, for example, the complex square root function (for $z\neq 0$) is two valued, but for any small region of (non-zero) complex numbers you can make it single valued by picking one branch. Riemann had a vastly better idea: there is a two-sheeted covering surface for the complex plane (ramified at 0) with square root a single-valued function on that cover.
Serre, who was well aware of the connection to Riemann, found a theory of 1-dimensional cohomology that worked correctly for the Weil conjectures, using not sheaves but fiber bundles, where a fiber bundle is considered locally trivial (and called "isotrivial"), not when it restricts to product bundles on small enough parts, but if it can be made into a product bundle by pulling it back along such a cover.
Well, Serre also saw how he could state the algebraic conditions needed to make this work, not only over the complex numbers, but over any field. Those conditions are now taken as the definition of a finite etale map. Grothendieck, with Artin and others, including Serre, made it work in all dimensions and for that purpose preferred to drop the requirement that the map be finite.
As to this works for the Weil Conjectures, let add a bit on why Serre first thought his "unramified maps" (which later gave way to the slightly different etale maps) were the way to such a cohomology, and why Grothendieck then decided this was exactly the way. You should combine this with Peter Dalakov's concise modern statement of the facts in his comment, and Will Sawin's beautiful account of what a cohomology theory for those conjectures would have to be like.
No one who was interested in the Weil Conjectures when they first appeared believed fields in finite characteristic would support any close analogue to the analytic topology on complex numbers. In hindsight people today pretty much agree with that, but at the time most considered this a decisive obstacle to any cohomological proof of the Weil Conjectures. And no one before Serre's FAC saw how to use Zariski topology to prove any very serious results. Serre's FAC immediately persuaded a lot of people that algebraic geometry over arbitrary fields could, and in fact must, use the Zariski topology.
But many structures which intuitively ought to be "locally trivial" are clearly not so if "locally" means "on small enough Zariski open sets." Zariski open sets just never are small -- they are dense on any connected component. Serre wrestled with precisely this problem for several years. And then in 1958, with Riemann's original works explicitly in mind, Serre said let us allow "local trivialization" of fiber bundles just the way Riemann "trivialized" multiple valued functions into single valued ones-- let us trivialize them by pullback along unramified Riemann surface covers -- except using a purely algebraic definition of "unramified" so it works over any field, and indeed for varieties of any dimension. A strikingly plausible idea once you think of it. But does it work?
By the kind of deep, detailed skill that Serre typically conjoins to his insights, he got it to work for dimension one cohomology (of varieties of any dimension). It works in the precise sense that it delivers the $H^1$ part of the long exact cohomology sequences you would want for the Weil Conjectures.
Serre knew well how hard he had to work to get these $H^1$s. So he was skeptical when Grothendieck first announced this had to work for cohomology in all dimensions. But Grothendieck had utter faith in his general theory of derived functor cohomology: once Serre identified the correct basics, they had to deliver the whole theory.
Well it turned out to take a lot more specific work, and there is the long and on-going story of the standard conjectures which were meant to make the cohomological proof much simpler than it yet is, but Grothendieck's faith was essentially justified.
As to the history I would slightly modify what Will Sawin says. He puts the key issues very well. But Weil did not believe there could be an actual cohomology theory for varieties in finite characteristic. I believe he believed there would be some more direct comparison theorem between varieties in finite characteristic, and their lifts to characteristic zero, which would make the conjectures follow from simplicial cohomology. And he did not especially believe that such a comparison would be the way to prove the conjectures. He probably leaned to the idea that the relation to simplicial cohomology of complex manifolds would be an enlightening corollary to some other kind of proof.