|If you want to fly your C reg aircraft in the US, your Canadian CPL+IFR is all you need (plus EAPIS, CBP sticker, POE for customs, and all that jazz)
If you want to fly a N reg aircraft, you generally need a FAA Certificate (Licence). You get the FAA to verify your Canadian licence and rating with AFS760 (Form AC 8060-71).
Then, if you're after private privileges, you can get a certificate based on your Canadian licence and medical. If you want your instrument rating on it, you'll have to write the US IFR conversion test (not sure if there's a flight test component for the IFR rating conversion - don't think there is.).
Alternately, you could convert to a full FAA Private Certificate. Pretty much all the above plus a US Medical and then it doesn't depend on your Canadian licence.
For commercial, you must do a full conversion + medical + test + fllight test.
For ATPL, you must do more tests and a special course (that I know little about - search for it).