ElChan bloody well done. That sounds like a very hard conversation but it needed to be had.
What DP said was incredibly hurtful and quite clearly untrue. It sounds like he's lashing out. Not to excuse him, but I guess he was able to pretend everything was OK and you were all managing as long as nobody talked about it. He MUST know, somewhere, on some level, that this isn't sustainable for anyone, including him.
I agree with PP that if he can access some counselling that might be a very good idea. I imagine the army is exactly the sort of place where they expect you to keep going no matter what, but he's back home now and has to unlearn that habit. The army owes him some help with that!
As for the social worker... Maybe stay focused on the problem at hand, sorting out some sensible respite arrangements. Personally I don't think there's any harm in mentioning that you as a couple are struggling a little but you will know better how DP will feel about this. Maybe raise it with him first? The social worker can't provide appropriate help if she doesn't know exactly what is going on.
The conversation with her about DSS's long term future can wait until you and DP have had a chance to feel more comfortable with the idea that it's not necessarily at home with you. But again, you'll know DP better - if it would help him to have some very noncommittal no strings attached information about what the options might be, then perhaps it is worth raising.
Well done, that conversation was just the starting point and there will be many more (maybe harder ones) before you reach a resolution, but it sounds like this is a relationship worth fighting for, for you. Keep coming back if this thread is helping, I remain in awe of you (and of your whole family really!).