This episode does both things it's trying to do exceedingly well.
The Stargate Wiki has a good write-up for you here,
There is a certain upper limit on how impressed I can be with an episode like this that's not trying to make huge statements about its characters or morality and that has no designs on being "big" in the dramatic sense or delivering a ton of comedy. Those are usually the kinds of episodes that score feature ratings here. BUT...for what this episode is, it's fantastically well executed. From what I can tell, the story is trying to accomplish two main things. It's trying to humanize Sam Carter and get us to view her as the stunning woman that she can be when she's not covered in dirt and shooting at bad guys (not to mention show us her sentimental, feminine side), and it's trying to access all of our inner fears about our vulnerability when dreaming and put a nightmare twist on it. The way that the two plots merge in the finale seems a bit hard to believe, but that's OK because the outcomes are good, and we can live with a bit of artistic license if it makes us happy.
On the first goal, this has to be the best romantic writing Stargate has ever done. Pete and Sam have almost explosive chemistry and the concerns of both characters are depicted fairly and maturely. I love that Sam is a traditionalist and likes old-timey swing dancing and classic movies. I love that when she dresses up, she not only looks unbelievably cute, but also elegant, rather than ostentatious and modern, and I love that the both of them are shown to really enjoy "the pursuit" (of dating). Their little games are a genuine treat to watch...you actually don't see enough of that in Hollywood these days. Yes, she and Pete have sex during this episode...it seems to happen rather suddenly and I think the episode would have been better without it, but is provoked by some very traditional impulses and I have to make an uneasy peace with the reality that romance necessarily equals bonking in the world of television and film.
On the second goal, the only thing that would have made the episode creepier would have been for us to see only the flash of Sarah's Goa'uld eyes in the darkness in the first shot of her in Daniel's room, rather than having her lean forward into the moonlight. That would have been so much cooler! But otherwise, the dream elements were done really well and all of Daniel's various conversations with the rest of the cast were well written and enjoyable. They even made room for some humor between Daniel and Teal'c ("Thank you Teal'c...this conversation has been deeply disturbing on many levels." - LOL) and, sensibly, had Teal'c be the one to figure out what was really going on. All in all, the show is well worth watching and a lot of fun, even though it's not overly ambitious.
Romance, humor and psychological suspense all in one episode and all done very well. Bravo!
Actually, this episode could have been a feature if they'd been gunning for something a bit higher on the drama-meter, and a lot of it has to do with the acting. David DelOuise is shockingly good for a low-profile guest star and Amanda Tapping plays feminine desire better than she plays military leader, and she plays that pretty well. We'd highlighted Anna-Louise Plowman before for her work as Sarah/Osiris and we'll do it again here. She and Shanks ALSO have good chemistry and she plays evil just as well.
I ahve to temper my enthusiasm just a bit for the modern take on proper courtship...but I can't hammer every show I watch that has some pre-marital sex and doesn't show the consequences accurately and this one also features Sam revealing a very traditional set of desires that gives her a ton of charm...so...we'll go easy.