Q:

Difference between dissonance and clashing notes

Wikipedia describes dissonance as the quality of sounds that seems "unstable" and has an aural "need" to "resolve" to a "stable" consonance
Which basically means it hurts your ears and you want it to go away.
In the band pieces I have played, there is often dissonance written into the piece. And it sounds good if the instruments that are playing the opposing notes are in tune. But then there is just times when there is an instrument playing a wrong note. And it sticks out. So my question is why does dissonance sound good at times, when it doesn't at other times?
I think that this has something to do with the context that it is played within. For example, I played "Aquarium" from "The Carnival of the Animals" as a percussion ensemble piece. I had a vibraphone part, and it called for an alternating E and F with the pedal down. And it didn't sound too bad when we played it together. But if I played alone, it sounded awful. Why is that?

A:

Dissonance is highly subjective and relative to musical context.
It is also commonly used as a source of tension in various kinds of music. Take, for example, a V7 - I chord progression. The dissonance between the 3rd and the 7th of the V7 chord (forming a tritone) creates a tension that we expect to hear a resolution for, by "relaxing" in the next, "stable" chord. This feeling of tension is mostly based in one's cultural upbringing.
Consider, also, the difference between a Maj7 chord as used in a slow jazz ballad vs. being played as a wrong note in baroque music. When we listen to different kinds of music, our ear is contextualized into the kinds of sounds we would expect to hear from that music. Hence, wrong notes are much easier to pick out in baroque music than they are in atonal music.
So, there's really no such thing as absolute dissonance (or consonance) in practice, but the relative terms with which we describe different collections of pitches come from an inspection of the harmonic series. You can read all about it on Wikipedia if you're not familiar, but the way it is applied here is that intervals near the beginning of the harmonic series can always be described as more consonant than intervals occurring later in the series.
So, to wrap up and come back to the last part of your question, when you were playing vibes in Carnival of the Animals, the part was occurring in the larger context of a piece of music with lots of textural elements, and the less-restrictive harmonic context of Saint-Saëns's French Romanticism. However, when you were practicing on your own, you were just hearing two notes in isolation that form an interval occurring quite high up in the harmonic series.

