SSIS ADO NET Data Source Parameters does not work the same way as OLE DB data sources parameter. Let us check out the OLE DB and data source and compare the ADO.Net data source with it.
Then click on the Parameter… then specify the the parameter for the ?
Now let us drag and drop a Ado.Net source to a data source. Then type
Now let us open the Ado.Net data source and hold our SQL script. Note I have used Union All as a garbage destination while developing a package instead of connecting a real destination Union All can be used.
There is no button here to enter the expression and even if you look at the data source property there is no entry for expression
So what we should do is go back to the control flow tab and select the data flow where we have the ADO.Net data source.
Then from the list of property types select [Ado.NET data source name].[SqlCommand]
Then enter the expression for the SqlCommand
Enter your expression and click on Evaluate Expression the see it is good then click on OK
After you clicked on OK you will see fx on the Data Flow Task.
After that everything will execute any other data source