Although I haven't had any hands-on experience, people I trust say that although it made them dizzy at first, once they played with the 3D slider a bit and found the sweet spot, it worked perfectly with no problems, and the effect was great. Moving it towards or away from your eyes can help, too. Everyone's eyes are different, so this kind of thing is to be expected. Don't take this as absolute fact (again, everyone's eyes are different, and this is a second-hand report after all), but everyone I know who played the 3DS has reported this.
As for me, I'm definitely getting one, but I just don't know when. I don't mind the battery life, and there seems to be some good games, but it can wait for a while.