The script needs to be saved as an "Application" and it should "Stay Open" (there are options for both of those in Script Editor's "Save As..." menu item). The return 120 line means that the script checks the network every two minutes (120 seconds); change this if you'd prefer a different interval.
on idle
try
do shell script "curl www.apple.com"
say "Your DSL is now available"
display dialog "DSL now available."
on error
-- say "DSL still down."
end try
return 120
end idle
[robg adds: Of course, since this script tests for any network connection, it's not restricted to testing your DSL connection; just change the text to match your connection type.]

