Like most words in the English language, Sissy has multiple meanings, some of which aren't even limited to the masculine gender. For example, it can mean a mean any person who is cowardly and timid. When I was young the term if applied to another child merely meant a boy who wasn't rough and tumble in the playground, but when applied to an adult meant someone who was effeminate and putatively homosexual. It is more popular nowadays to describe a Sexual Fetish of a male who wants to act/perform as a submissive female, with or without French Maid or School Girl costume and other external evidence of forced feminization.
Whether it is an insult depends more on the mindset of the person using the term than that of the person to whom it is applied, doesn't it?