1066, Year of Viking Destiny: Strictly a war scenario, nothing special. Interesting to play defensively as England as they immediately get overrun but fairly easy to turn the tide. It was also fun to take over England with the Norman knights. Both groups of vikings played the same as Denmark so nothing new there.
American Civil War: Another war scenario, I felt it was a bit anticlimactic and lacking. It wasn't terribly difficult as long as you bring enough cannons to weaken the capital's defenses. I did enjoy the unique promotions given to units and the history lesson that came with them.
Conquest of the New World: Fun to play a few times. European civs play the same, native civs play the same. I enjoyed sending the caravels to China and settling by the natural wonders but after you've played through it a couple of times, you kind of know how it's going to play out. It is nice the map varies each time though which does add to the replayability a little. I never tried the deluxe version.
Empires of the Smoky Skies: I like the idea of the victory conditions but it's pretty much a normal game with some altered units and is easily winnable. I played as the leader with the production UA... won easily and never played again.
Fall of Rome: I played this solely for the achievements. Playing as one of the Romans is a pain... the deity challenge was more of a slog than anything. I found that victory wasn't really about how well you play but whether or not the AI (particularly the Sassanids) dominates or not. Their score keeps going up and up to the point that simply defending and keeping your own cities isn't enough which seems a bit silly. If it was solely a matter of defending your cities to win, I'd like it more, but as is, it's too tedious for me to want to play again as Rome. The Vandals were the most entertaining for me.
Into the Renaissance: I could only play the random maps as the historical map was too large with too many units for my computer to handle. Again, fun to play a few times but it can sometimes be more of a chore than anything on the high difficulties. The whole religion aspect seemed pretty pointless other than giving the AI a reason to hate you, which is a shame since that's a core mechanic that the scenario was based around. I'd like it better if it were shorter.
Paradise Found: Like other scenarios, fun to play once but after that it's the same thing over and over. Fun idea, fun to find the real world geographical locations, gets stale after the first playthrough.
Rise of the Mongols: Maybe my favorite scenario playing the deity challenge. It's pretty fun wiping out every city on the map and it's nice that killing city-states is actually worthwhile in this scenario. Happiness reaching -100? Who cares! The keshiks keep dominating until everyone is dead.
Samurai Invasion of Korea: I found this to be decent. Depends who you play as. I found it most fun playing as Manchuria, second would be China. You practically can't lose as Korea and it can be a bit of a slog taking down China as Japan.
Scramble for Africa: Playing as a European power was pretty easy to dominate. The North African gold victory wasn't very fun for me. I did enjoy the culture victory, particularly the Boers on deity. The Boer deity challenge was probably one of the more memorable scenario victories I had.
Wonders of the Ancient World: Likely tied with the Mongol scenario as my favorite. It's fun to comb the deserts for ruins and I like how you have to unlock wonders by reaching certain benchmarks in the different categories.
If I had to rank the scenarios from most enjoyable to least enjoyable, it would probably look like this:
1. Rise of the Mongols
2. Wonders of the Ancient World
3. Scramble for Africa
4. Samurai Invasion of Korea
5. Conquest of the New World
6. Into the Renaissance
7. Empire of the Smoky Skies
8. American Civil War
9. Fall of Rome
10. 1066: Year of Viking Destiny
11. Paradise Found