Not sure which light console would be the best for the application you seek. You will have to research that.
Many high tech, MIDI equipped lighting boards will receive information on several MIDI channels. So, not only do you have to make sure it is reading the right midi note on/off information, but you must be sure that both the drum module and the light board are set to send and receive on the correct channel.
Some older boards do not recognize midi "note on/off" information but will recognize "event" data such as momentary on/off or channel on/off , start/stop information, etc.
Some newer boards have a "learn" function that will read the incoming note on,etc. info and display what note # and MIDI channel is being received, and display that information to allow you to assign a specific function.
I have not used MIDI to control lights before, but I have had extensive programming experience of multiple drum machine, sequencer, sound module, effects unit devices and I am aware of many light systems capabilities to utilize MIDI event data.
Once you have determined the correct channel and number than you must assign the function that you wish to trigger. This is done on the light console. You will first need to determine if the unit requires a note on AND a note off message, or will it simply react to the single note on trigger. Some units will even have a trigger sensitivity or a threshold sensitivity function that will read how hard or how long the trigger is hit. The light console may or may not be able to process this information.
You will then need to assign a function to that trigger information. Such as, light channel #5 set to receive note on information from MIDI channel "X", Midi note number "Y" , etc. The board may let you pre-program a "duration" in relation to how long that channel will turn on when it receives the note on trigger information. There may be several other factors to consider also.
This is a cool idea, but keep in mind that you may annoy the heck out of other band members who get sick of seeing that yellow spotlight come on every time you hit a bass drum!!
You definitely have your work cut out for you!