As it's been said elsewhere, it will depend on how it's executed (Badum, tch!).
Personally, when I played the game for the first time, FFVII had been freshly released and there was no way I saw it coming. In the aftermath of the Temple of the Ancient segment, when you have the scene in Cloud's dreams where Aerith leaves for the Forgotten City and Sephiroth/Jenova chases after her, I initially thought "oh, well, classic case where the hero goes on a journey and manages to save the girl in the nick of time". So when *THAT* moment happened, I wasn't sad - I was shocked. Very much how something like this would happen in real life, the first feeling was utter disbelief. I remember saying: "if the game follows through with it (without bringing her back somehow), that's the most ballsy move in video gaming history!". I was quite in awe that the writers had dared doing that.
The sadness only began to seep in when I realized "we have to move on without her", and the heroes have to continue on their journey to the Northern Crater.