SSIS: Get Date for Day of the Week
You can use the following code to get the date for any given day in the current week:

DECLARE @DesiredDay INT = 0;
DECLARE @OutputDate DATE;
SET DATEFIRST 1; /* Monday is day 1 */
SET @OutputDate = DATEADD(d, @DesiredDay - DATEPART(DW, GETDATE()), GETDATE());
SELECT @OutputDate;


I'm setting @DesiredDay to 0 since you requested Sunday in your question.  For Monday, set this to 1, for Tuesday set it to 2, etc.