SSIS - How To Pass Parameter Value In OLE DB Source in SSIS Package

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')


Step 1: 
Create Variable with name Dt  in SSIS Package as shown below
Create variable in SSIS Package so we can use in OLE DB Source

Step 2:
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




3 comments:

  1. Thanks Warraich.....never knew this though i have used oledb source many times.

    ReplyDelete
  2. But How can i pass the sql query results(which will be single value(not linked servers)) form another server as parameter value.

    thanks in advance

    ReplyDelete
    Replies
    1. Hi Anil,
      Here are steps for that
      1--Create a variable
      2--Use Execute SQL Task , in The result set use the Single row, paste your query in SQLStatement and then in Result Set Map to the variable you have already created.
      Once you have the values in variable you can use them in above example. In other words your package will have execute sql task first and then data flow task.
      Here is link how to use Execute SQL Task
      http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

      Thank you

      Delete