Not sure that you need a dynamic sql statement in Ole db source adapter, via a variable. To use something like a date variable I can simply construct my query like the following:
Select col1, col2 col3
from myTable
where dateField >= ?
Use the Parameters dialogue via the button to map the '?' to your date variable.
You also mention :
In my example, I have created 3 tables as shown above – dbo.TGT_tablename_tbl, swap.TGT_tablename_tbl and shadow.TGT_tablename_tbl.
The names here are not consistent with the diagram. That would be good to have.
Thanks.
----------------------------------------------------