Is there an iPad app to play movies from an SD card?

The "Camera Connection Kit" is called that for a specific reason. The USB and SD Card readers are meant to either directly connect your camera to it, or just the SD card from it.

This means also that the readers only function with the Photos application, and nothing else. This is also the reason why most external keyboards don't work anymore.

There is no SDK access to the Camera Connection Kit, so third-party apps may not use it.

IIRC, Movies taken by your camera are imported into the Photos app as well. I do not recall whether or not you can view them from within the Photos app.