One more way!

1. Get Today date from Script Task and store it in an variable.

Now.AddDays(0).Date.ToString("MM/dd/yyyy")

2. Pass that variable in your DB Source along with your condition.

SELECT <Column Name>

FROM TableName

WHERE .... =/> <Variable Name>. Use your convert function to convert as per req.