I use the SquirrelSCSI with an external CD Rom, the CD32 Emulator software that comes with it works pretty well on my A1200. I used to use IDEfix97 with an IDE CD drive hanging out the side of the 1200, but that was years ago, and I don't think the CD32 Emulator it had was quite as good as the Squirrel's.
Anything that requires the Akiko chip or FMV module is a no no though. Some games like the Lotus trilogy just use the Akiko for some kind of copy protection so won't work despite not using it for its C2P functionality :-( .
You'll need an audio mixer to mix the CD audio output with the Amiga's audio if you want to hear CD audio. You could make one relatively easily, or use a cheap 2 channel DJ type mixer etc.
There is a hacked version of the CD32 Lotus Trilogy in existence that works well, albeit without the CD audio on AGA machines (it may also work on ECS, as I don't think any of the Lotus games are AGA specific). Also an AGA conversion of Wing Commander exists, based on the CD32 release which is pretty nice in 256 colours on any A1200/4000 with an 030 or above.