A lot to think about and a few things to respond to thanks!
There's a (20 year) back story and while I didn't intend to drip feed it hadn't really occurred to me that this behaviour is probably part of it.
Dsis works full time to support him and their 2 kids. She works in a high paid professional job and has worked hard to get there. I don't know anyone who works harder - at everything.
He's been out of work persistently, with occasional glimmers of hope, for several years. But he now has a part time job (16 hours a week). She probably (rightly) thinks that as she's working so many hours, she shouldn't have to come home, collect the kids, sort the kids' homework out (which she invariably does) and walk the dog. She will have a go at him, he either switches off entirely and ignores her telling her what a nag she's being or promises he'll change.
I agree that dsis is complicit, but it's very hard to explain the kind of relationship they have without sounding mean spirited. He most definitely does have issues and I would say is heavily abusing alcohol and has done so for most of their married life. Dsis is a 'stand by your man' type who is very loyal and patient with him. Every so often she'll challenge him about something (lack of work, drinking too much, overfeeding the dog etc) and he'll give lots of excuses why he does it, promises things will be different, goes through some motions etc before reverting quite quickly to old ways. He tells her what she wants to hear, she believes it will all change, and then plods along the same route until she realises nothing has changed and something sparks it again.
I do think there's an element of depression and with the drinking it's a vicious circle - whenever he gets a job, he begins to look for reasons to justify to dsis why he can no longer do it. The excuses are usually pretty unbelievable to an outsider. He quits/ loses the job and drinking fulls his days again, he gets depressed again. But he has held down a job before for a number of years and when he puts his mind to something he can be very hardworking.
I have no doubt that he had a shitty start to life. On the plus side, he can be very generous, sensitive and loving. But he can also be a bit of a bully (verbally) and very stubborn and manipulative.
He knows that his childhood wasn't great, somewhere deep down knows it's left its mark on him, but blames other situations and people instead and has never sought help.
I'm not sure my dsis knows (she definitely doesn't admit it) how damaged he is or sees his actions as odd any more - she's got used to it.
The more I write and read back, the more I realise the problems are quite severe and nobody can resolve them except dsis and bil. But I just don't think either of them have the strength to make permanent changes. As a sister, it all feels a bit hopeless actually, especially as I know she'll never open up, admit there are huge problems and ask for help. She's exceptionally private and loyal to him, to the point where she is deluding herself about their entire relationship and family life. 
I don't think reporting to the rspca is going to solve the root problem and will probably serve to drive dsis further away. Bil will use it as a way of convincing her that everyone else has the problem, but not him. She'd be hurt by my actions, he'd capitalise on that.