Following are the steps I used to get parameters mapped to my SQL query using DataReader Source  and ADO.Net connection manager.

1. Create Ado.Net Connection manager using .Net Providers.

clip_image001

2. Add DataReader source and map it to ADO.Net connection manager by right Click -> Edit.

clip_image002

3. Under component properties, Write SQL Command without where clause.

clip_image004

4. Verify Column mapping and click ok to close the DataReader source editor.

clip_image005

5. Go to Control Flow and go to properties of Data Flow Task. Notice a property called Expression and an a small button next to it. Click on this button to open expression editor.

clip_image007

6. In Expression editor select[ DataReader Source].[SQLComamnd] under property column and hit small button under expression column, This would open another editor.

clip_image008

7. Write in your SQL query appended with variable names and hit Evaluate expression button

clip_image010

Note: On hitting Evaluate expression, You could see evaluated value box populated with variable value(in my case its date 12-07-2005 04:01:00.

8. Rest of the stuff is same, You could go to data flow tab and map the output of your DataReader task to valid output task.

clip_image012

Note : Execution succeeded but there is still a red cross which I guess is there because of same runtime evaluation of expression V/S design time value. I am not sure if there is something that could be done about it but I guess for now if it works, You could safely ignore it.

Doing similar operation with OLEDB Source

Create a data flow task and place an OLEDB source and Flat text Destination.

clip_image002[6]

In OLEDB Source’s SQL command, I used  select * from sys.sysprocesses where last_batch >= ?

clip_image003

And mapped query parameter with MyDateType by clicking Parameters button.

clip_image005[6]

Although preview won’t work at this point but if I would execute the package, I see query with DataType parameter fetching relevant data without any exception and dumping it in text file for validation.