According to the good folks at Merriam-Webster, a conniption is a noun of unknown origin, with a date of first appearance in 1833.
It is a term synonymous with having a fit of rage, hysteria, or alarm.
From my own experience it is used in a derogatory fashion, as a way of saying someone has lost emotional control. It's a common term in my region of Appalachia. By way of example, if someone asked "Whatever in the world is wrong with your aunt Maybelle?" you might reply "I don't know, she's just having one of her conniption fits." It carries a connotation of an over reaction to the causative event.
Sometimes the word fit is omitted and the person in the throes of the emotional storm is said to be having a conniption.
The term is akin to other descriptive phrases such as hissy fit or temper tantrum. There are subtle differences between them, however. A temper tantrum involves only the emotions of anger or frustration while a conniption fit can embrace confusion, exasperation, anger, fear, or other emotions.
Sources:
http://www.merriam-webster.com/dictionary/conniption
http://www.wisegeek.com/what-is-a-conniption-fit.htm