Using Dynamic Query in SSIS Data Reader Task

There are certain requirements in which user has to load data for a certain time period or based on some condition which changes dynamically.
For OLEDB task we have option of parameterized Query but what about Data Reader Task ???

Here I am taking an example in which user has to data between two dates, present in a table.
Following are steps for it:-

1. Declare two variables type String as shown in figure.



2. Use Execute SQL Task to get execution dates into SSIS variables. Write following Query to get dates from ExecutionDates table.

select convert(nvarchar(20),Convert(nvarchar(10),ToDate,120)+ '  00:00:00') as 'To',
convert(nvarchar(20),Convert(nvarchar(10),FromDate,120)+ ' 00:00:00' ) as 'From'
FROM dbo.ExecutionDates

ExecutionDates table having two cloumn name FromDate and TwoDate for defining Executiondates.

3. Configure the Execute SQL Task according to the following screen





4. Now drop a data flow task, Connect Execute Sql Task to data flow task. Inside data flow task drop a data reader Source from ToolboxData Flow Sources. Paste following Query. Now you can put query without where clause because it’s going to change dynamically. So condition doesn’t matter.

SELECT * FROM Sales.SalesOrderHeader
WHERE ModifiedDate = getdate()

5. Now go to data source properties.  Expression  [DataReader Source].[SqlCommand] property
Following Screen will show you how you can configure it for Dynamic Query.








The final Query expression will be like

"SELECT * FROM Sales.SalesOrderHeader  WHERE ModifiedDate >" +  @[User::From]  + " AND ModifiedDate <=  " + @[User::To]

6. This way you will be able to evaluate query at the time of package execution.


This is how we can set Dynamic Query for Data Source reader.



Thanks,
Gaurav Gupta