Yes, it needs to be enjoyable to play, absolutely. Well I think so at least.
If it fails at this, then it is a bad game, regardless of its 'artistic' merit or emotional engagement. You can have these things (it would be good if more games did), but in doing this you can't forget what games are in the process.
Dear Esther is a good example of how not to do it, unless they fixed this issue in the full version, I have only played the free mod a while back. It has the engaging narrative which is emotionally satisfying no doubt, but falls very short everywhere else and it not enjoyable to actually play at all.
Amnesia: The Dark Decent is how it is done properly, it has the emotional engagement, without sacrificing the enjoyable gameplay.