I saw a thread on rpg.net about this, and it's a question that's come up several times in discussion with new players, especially ones who are thinking about DMing but intimidated by the concept. I've talked with several people I consider excellent referees about this over the years, and thought I'd share the fruits of those discussions.
The first thing to get out of the way are modules. I don't actually know very many referees I would consider excellent who rely on published modules, unless they've written them themselves. The best referees I know will occasionally pilfer a map, or a character idea, or a statblock, but the elements tend to be pulled out and inserted into their own games rather than run straight. The less story-driven a module is, the more useful it seems to be, since the group is busy telling their own stories. Also, narratives in modules tend to be written and presented in exactly the wrong sort of way to make them useful. There are rarely timelines or relationship maps, or sensible, coherent descriptions of NPCs' motivations. An evil mastermind's plan presented as a simple flowchart would be a drastic improvement over the longform notes presented piecemeal throughout an entire module, which seems to be the norm.
The second source is imitation. I know a lot of people who use television, film, and books as inspirations for adventure (and campaign) ideas. I'm not personally much of a fan of this for my own games, but I have seen it done well. The most successful adaptations of this technique tend to involve two or more different sources that are blended. When I see this process done well, it reminds me of one way professional screenwriters create compelling dramatic characters, which is by combining two real people's qualities into one person. The worst imitations tend to be the "X + Y" type, where two sources are layered on top of one another rather than blended. Often this involves merely transplanting characters or ideas from one source into the dramatic situation of another source. I also count recreating historical situations as imitative, though I do use this far more than television, film and books as a source.
The third type of source is the setting itself. This is more common with published or well-established settings, and I think that one of the reasons metaplots in published game lines are so popular is that they give you an idea of what kinds of adventures PCs should be having. This is the one I tend to draw on the most, since I love world-building. A good campaign setting should have plenty of possible frames (the initial situations that define and incentivise the goals PCs will pursue), and I think that defining these kinds of frames does far more to make a setting playable than does detailing every innkeeper's name and stat-line or every hex (though these can be useful as well).
The fourth I'll call the "imaginstic" source. The best referee I know, my buddy Curtis, uses this one a lot, as do I and many others. This is where one gets an idea for a character, situation, image, etc. Some singular element that isn't necessarily tied into anything else or part of some greater structure. One then starts from this single idea and develops an adventure or campaign idea based on it.
The last two, despite being far more productive and useful in my mind than the first two, do have the weakness of basically pushing the problem to the second-order - how does one get the images or ideas, how does one pick out the elements of the setting that will be most amenable to being gamed?
The answer is "creativity", of course, but "creativity" isn't really an innate, divine talent that one receives at birth. So here's a simple exercise that can lead one to develop adventure ideas.
Exercise: Breaking things
The point of this exercise is to take a happy situation and remove or degrade elements of it until it is an unhappy one. You start by imagining a happy situation according to the trappings, settings, or whatever other constraint you have. For example, it might be a well-run fantasy kingdom at the medieval level.
Then, you list out all the elements you consider relevant to creating this situation. The kingdom might have a just and popular king, righteous nobles, industrious peasants, religious harmony, abundant natural resources, peace, prosperity, and fair laws. The more specific the better, but even broad strokes will work.
Then you start inverting or removing these things. So "religious harmony" becomes "religious strife". Invert or remove them one at a time. Each time you do, assume that at some point the good thing existed, and tell a little story about how and why it stopped existing. I recommend stories that are exactly three sentences long, each one describing one event or change that happened along the way. I also recommend they mention one or more specific people who do things.
"A monk began preaching that the dogmas of the church were lies and was excommunicated. He built an army of followers who defended him from the church. Eventually, his followers attacked the church in a crusade."
Sentence 1 is your set-up or backstory, sentence 2 is the present situation when play begins, and sentence 3 is the future unless the PCs act to stop it. The person mentioned in the sentences is either the ally of the PCs or the antagonist whom the PCs oppose.
Once you have two or three of these little stories written out, you should have enough material for at least one adventure if you combine them all. If it's not, continue writing these out until you have enough, or check to make sure your stories have enough specificity, and aren't just broad strokes. It can help to make all three sentences describe the specific actions of the person at various places and times.
I could rewrite the above as "A monk began preaching that the dogmas of the church were lies and was excommunicated. He preached a great sermon at Colchester on Easter and converted many people who defended him. He spoke to them a month later in London to convince them to kill their local priests and burn their churches."