You started working FT in September - when did these weekend sleepovers start? If it was around then, I'd be deeply suspicious that he was trying to make it difficult to hold down your job. But it also sounds as if loading you up with work has been his hobby ongoing for a loooong time to, so probably not.
It very much sounds as if your husband has a vanity problem. He wants, as you put it "taken a role of a family 'ruler' and problem solver" . Also, "Parents/grandparents all live few hours away, so not very involved" - the 'parents' being his sibling and their partner, 'grandparents' being his parents/your in-laws. Are there any more family around that he likes to assume responsibility for? Any other nieces/nephews/cousins, or is it just this one nephew? If there are more family, is he the generation 'up' from them (regardless of actual ages)?
If it's just this one nephew, it's easier, because then you've just got to deal with one couple directly. Just as you texted the mother that this weekend was not happening, you text BOTH of the cheeky fuckers that there will be no more sleepovers. No ifs, no buts, no excuses, no justifications; just that there will be no more sleepovers.
If there are other family members for whom DH is the Problem Solver, do similar. No ifs, no buts, no excuses, no justifications; just that there will be no more lifts / money / outings where you always pay / whatever family shit he has signed you up to provide. No more.
That leaves the problem of your petulant sulking husband.
Why did he do what he did? Well my guess is that this makes him feel like a very fine fellow indeed. It's about how he sees himself - Head of the Family, Problem Solver, the go-to guy and all-round Good Guy.
You telling him sleepovers are off has held up a mirror to him and shown him that he is none of these things; that actually he's a bit of a fraud, riding on your coat-tails.
All you can do is point out to him that his primary responsibility is to his own children, and his constant dumping of a 6-year-old on them is twattish to the extreme. Your eldest is coming up to the important years educationally, and should not be hampered by a chaotic home / mewling child / however you care to put it to him. I'd also point out that he's infantilising the family members to who he masquerades as Problem Solver - how will they ever learn to solve their own problems if he keeps stepping in? His 'kindness' in the short term actually works out as being very unkind to his nephew and nephew's wife, in the long term. And so, it needs to stop.
He won't take kindly to being shown that he is not, in fact, a Good Guy. That he has been kidding himself. Anticipate more petulance and sulking! But hold firm. He's just going to have to accept that any 'support' he offers family members will have to be provided by himself (and ONLY himself). He may actually do that, or he may learn not to offer what he won't provide. Either way - hold firm, and ride out the petulance and sulking.
(Oh, and don't cook for him tonight. His first lesson should be that you'll take him at his word when he says he doesn't want to burden you. In other words, don't cut off your nose to spite your face.)