Don't know how much real redundancy you'll get with two DSL lines.
Since both lines would end up going over the same physcial wire bundle to the same central office. You'd only be protected in the event you lost one of the DSL modems or one of the central office interfaces. Any problem of greater magnitude (wire bundle severed, fault at the central office, failure of the upstream interface from the central office, etc.) would kill both DSL connections.
You'd probably get better backup/redundancy by going with one DSL and one cable connection.
You can't really use two machines each with dual NICs and connect both of them simultaneously directly to both lines though. With more than one computer, you should use a router.
If you're insistent on automatic and transparent failover on a line failure, you'll need a dual line router like the Nexland Pro800. The cheaper approach would be to just manually swap the uplink line from the cable modem to a normal router with the uplink line from the DSL modem.
Line and/or ISP channel failure is much more likely than a failure of a NIC or even a router.