I think the problem you're seeing is that a phone call causes the
phone to ring, and continue doing so until answered, dismissed,
forwarded to voicemail, or the caller hangs up. An SMS notification
sounds for a much more brief period of time.
Neither of these processes are passed through Tasker; Tasker is only
notified that they are happening (or have already happened). The
processes continue independent of Tasker.
I think you're best bet for changing the notification sound for a
particular sender using Tasker is to make your default notification
sound silent, and then play a notification sound via Tasker - changing
the sound for the desired contact(s).