Scenario:Let's say we have to create a SSIS Package in which we need to extract data from one of the table but only for date provided in the variable.
Solution:I created a table and inserted few records so we can use in our SSIS Package. Here is script to create table and insert records
CREATE TABLE dbo.TblSource ( CountryName VARCHAR(50) ,Sale INT ,SaleDate DATE ) GO INSERT INTO dbo.TblSource VALUES ('USA',100,'2013-07-10') GO INSERT INTO dbo.TblSource VALUES ('Mexico',200,'2013-07-11') GO INSERT INTO dbo.TblSource VALUES ('Brazil',300,'2013-07-11')
Create Variable with name Dt in SSIS Package as shown below
|Create variable in SSIS Package so we can use in OLE DB Source|
In Data Flow Pane bring OLE DB Source and write query and then map parameter as shown in below
|How to map variable in OLE DB Source in SSIS Package|
Final Output :
Set the variable Dt to the date you want to pass as parameter to your query in OLE DB Source, In our example we passed 2013-07-11 and we can see the results by using Data Viewer. In real time you will be connecting your OLE DB Source directly to your destination. I used Multicast Transformation just for testing purpose.
|How to use SQL Query in OLE DB Source with Parameter in SSIS Package|