You could start with an execute SQL task to query your calendar table for the current date (or current date - 1, not sure of your logic), and put the start and end dates into string variables.

Then you can set up another variable with your data source query, and append the date variables, and then set up an expression in your main data flow for the sql command in your data source, putting the string in.