(((CA))) Welcome to SR and I'm glad you found us. If you've been reading around on this board at all, you have probably seen the 3 Cs used:
You did not CAUSE it
You cannot CONTROL it
You cannot CURE it
Your BIL's behavior with you has no doubt "crossed the line" IMO. I would have your husband privately confront him about it and just let him know that a) the behavior was unacceptable, b) it offended YOU and c) it will not be tolerated. This may open up a dialogue about your BIL's addiction, but do not expect that this will "make him fly straight" in any way shape or form. It is true that the addict must find his or her own bottom or they will not stop. I understand your concern about your sister or SIL and the 2 children, but what is SHE doing to take care of herself and her children? It is her that needs to figure out how she is going to cope with all this.
Also, if you feel uncomfortable enough with the situation that just happened, you may want to set a boundary with him. This boundary could be something such as: "we will not come and visit you if you are drunk or if you drink in while we are there." If you do make a boundary though, please follow through with it or it will be just as empty of a promise as the alcoholic makes.