Map DataReader & OLEDB Source SQL query parameter to SSIS variable

 

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.

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

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

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

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.

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

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

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.

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.

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

And mapped query parameter with MyDateType by clicking Parameters button.

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.

Comments

• Anonymous
  March 01, 2011
  Thanks a lot mate. really taken me out of a big trouble. Cheers and keep up the good work.

• Anonymous
  March 08, 2011
  Hi, In my case parameter is not taking passes value. Its just taking default value defined at the time of declearation. Can anyone help me out? Thanks Niyati

• Anonymous
  July 14, 2011
  This does not work if the SQL statement is over 4000 characters in length. A huge drawback to the DataReader source is that it won't accept parametrized queries!