As far as I know you have to do a timing type polling if you want to use the SQL Adapter. You can, of course, build your own adapter or get the record into Biztalk through some other method – like having triggers write message to a queue.
As far as dynamic input, as far as I know that is not possible. Receives into Biztalk through the SQL Adapter have to be known locations since it works on a polling bases.
Hope this helps.