What is the value of a dps caster?

Generally its not super great Sanzang and Illya are the best with Illya being able to put out damage numbers that you wouldn't think to see from a caster(47k damage on her NP) while Sanzang is able to use hers twice in a row which with proper support will lead to more damage. That said the .9 damage modifier hurts a lot as .9 x 2 = 1.8 while berserkers have 1.1 * 1.5 = 1.65 which when accounting for the fact berserkers generally have much higher base attack stats makes caster the only class that only ties berserkers in terms post class advantage damage while every other class will out damage berserkers when they have advantage.

In general though as you've pointed out casters are built to be support units and make poor attackers.

I fairly like casters on the lore with people like Medea (liked her background and her Lily version's is so cute I entered the game), Tamamo from fate extra and Semiramis, but honestly I think they're just there to balance things out and such that one class wouldn't be completely for support. For example the RNG gods didn't give someone berserkers, but has offensive casters?
I just raise them since I'm fairly clumsy at strategy and I hate using berserkers unless for farming or if it's cu alter, so it's more of preference. I'm with the side that goes with the clasa advantage thingys and likes stragtegizing without the worry of having my units get killed by a stray crit.
Also I want to destroy those phantom beasts or somethings that evade after NP.
But honestly the only DPS caster you would want is Nero summer with 17k attack with all her buffs on her at level 10, and considering modifier. You won't need another caster ST or AOE anymore.

The real value of a dps caster is just mainly to deal with assassins and not die so quickly since the majority of assassin bosses have some sort of crit-based ability that will surprise buttfuck your team when you least expect it. The other main reason would be just because you like the character.
Illya is one of the better offensive casters, not as good as Xuansang, but she still has decent np gen and a pretty strong np that can assblast most assassins and berserkers.

Yeah, as others have said, the only thing they do better than other servants is killing assassins while lasting longer than berserkers. That said, it doesn't mean they are useless outside of that niche, just not the best options. Xuanzang, for instance, is pretty well-made from a game design perspective imo - almost everything about her (skills, command card deck, stats) is streamlined for allowing her to spam her NP as much as possible. Due to this, even against neutral enemies, she is still fairly good despite her class's damage modifier. That said, other servants, ie, Ozy, Jalter, Gil, etc will be more consistent at dealing damage than her, but that doesn't mean she isn't good.

For Illya specifically, her use is as a nuke, kinda. Her NP deals higher damage than Xanzang's but comes with annoying demerits (redice ATK/DEF 10% for 3 turns). Her main role is as a nuke, kinda like Robin hood just with a lot less damage.