Creating a Dynamic Query in Lookup Transformation in SSIS

We can create a Dynamic Query by using Lookup Transformation in SSIS. Below is the procedure for creating the Dynamic Query:

Create the tables in a database as below:
CREATE TABLE TEST_EMP(EID INT, ENAME VARCHAR(20), DEPTNO INT)
CREATE TABLE TEST_DEPT(DEPTNO INT, DNAME VARCHAR(20))

INSERT INTO TEST_EMP  VALUES(1, 'SUNEETHA', 10)
INSERT INTO TEST_EMP VALUES(2, 'NAGARAJU', 10)
INSERT INTO TEST_EMP VALUES(3, 'SUNIL', 20)
INSERT INTO TEST_EMP VALUES(4, 'GANGADHAR', 10)
INSERT INTO TEST_EMP VALUES(5, 'LAKSHMI', 30)
INSERT INTO TEST_EMP VALUES(6, 'BHARATHI', 20)
INSERT INTO TEST_EMP VALUES(7, 'SWARAJYA', 40)
INSERT INTO TEST_EMP VALUES(8, 'NAGESWAR', 30)

INSERT INTO TEST_DEPT VALUES(10, 'Data Group')
INSERT INTO TEST_DEPT VALUES(20, 'Purchasing')
INSERT INTO TEST_DEPT VALUES(30, 'Call Center')
INSERT INTO TEST_DEPT VALUES(40, 'Communication')

SELECT * FROM TEST_EMP
SELECT * FROM TEST_DEPT

Create a package in the SSIS and name the package as DynamicLookup:

  • Create two package variables as follows:
  • Right click on the area in the Control Flow tab, select Variables
  • Create two variables as follows:
      1. DeptNo – This variable Stores the Department Number.
      2. Query – This variable stores the Query Statement that is used as SQL Command for the Dynamic Lookup.

     

  • Enter the value for DeptNo package variable as 10 and for Query package variable goto the properties by pressing F4.
  • In the properties window, make the EvaluateAsExpression property as “True”
  • In the Expression property select the Ellipses (…) button and enter as below
    “Select Eid, Ename from Test_Emp where Deptno = ” + @[User::Deptno]
  • Click on Evaluated Value to cross verify whether you entered is correct or not, finally click on OK button
  • Drag and drop the Data Flow Task from Control Flow Items in the Toolbox to Control Flow tab.
  • Double click/ go to the Data Flow tab and Drag and Drop the OLEDB Source Task
  • Click on New button for creating the new connection to the SQL Server to connect to the database where the table is created and click on Test Connection to verify whether the connection is successful or not.
  • Select the table TEST_EMP from the list of tables in the database in the Connection Manager page

  • In the columns page check which columns are required as source and click on OK button.
  • Drag and drop the Lookup Transformation (named as LKP_DynamicQuery) from Data Flow Transformations in the toolbox to the Data Flow tab and connect the lookup to the OLEDB Source task that is already in the Data Flow tab.
  • In the Control Flow table right click on the Data Flow Task and go to the Properties window
  • Go to the Expressions property and click on Ellipse (…) button.
  • Select [LKP_DynamicQuery].[SqlCommand] from the Property Combo box in Property Expression Edition and click on Ellipse (…) button under expression column
  • From the Variables tree select the user variable Query in Expression Builder dialogue box, drag and drop the Query package variable in the Expression text field and click on Evaluate Expression for verification and click on OK button.
  • Click on OK button in the Property Expression Editor dialogue box.
  • Go to Menu bar –> select Build –> select Build SSIS Excercises (Here SSIS Excercises is my solution name.
  • In the above figure we can also see the properties of the Lookup which has SqlCommand property as empty value.
  • Once we build the solution we can see that SqlCommand property in the properties is filled with the dynamic query that we have given as expression in the properties for the Data flow task
  • Right click on the Lookup and select Show Advanced Editor for giving the connection to the database.
  • Select the Connection to the SQL Server from Connection Manager and Click on OK button.
  • Open the Lookup Task and in the General page select “Redirect rows to error output” from “Specify how to handle rows with no matching entries.
  • Now in the connection page we can see the dynamic Query as follows
  • Go to the columns page and select the mapping of the columns
  • And click on OK button
  • Drag and Drop the Flat File Destination from the Data Flow Destinations in the toolbox and link it with Lookup Task with Output as Lookup Match Output and click on OK button.
  • Double click on Flat File Destination Task and click on New for assigning the new connection to send the matched rows from the Lookup.
  • Click on New button to create new connection for the destination of the flat file.
  • Select the type of Format in the Flat File Format window. Here, we are selecting as Ragged Right and click on OK button.
  • Click on Browse button in the Flat File Connection Manager to enter the path of the file and filename that stores the data from the lookup.
  • Goto each and every page and verify the columns and at last click on OK button.
  • Go to the Mappings page in the Flat File Destination editor and select the Ename_LKP for selecting the ename that are matched in the Lookup and click on OK button.
  • Save and execute the package by pressing F5.
  • Now we can see the package is executed successfully.
  • If we open the Flat file that was created we can see as follows.

 

Hope this will be helpful….

Leave a comment

11 Comments

  1. Sure I will

  2. Hi Suneetha Garu,
    The Post was really good. till now i do not know how to use lookup transformation with variables. so really wonder way and easy way to learn lookup with variables. based on this we can try some other examples. hope you will post like this always. n finally blog was too small. please enlarge and do.

    Thankyou,
    Saikumar Annapareddy, Andhra Pradesh

  3. GSethi

     /  October 13, 2015

    Great, this is what I was actually looking for. Thanks for sharing this. One question, it work if Lookup connection is a OLEDB For Oracle type ?

  4. Neil

     /  July 16, 2015

    Many thanks for this excellent solution. This should definitely be more publicised.

  5. andriy

     /  May 21, 2015

    Thanks! very helpfull!

  6. Anonymous

     /  October 25, 2013

    I never thought to look there, ever. Thanks!
    Darin

  7. Anonymous

     /  August 7, 2013

    thanx a ton

  8. Deepak Thirunavukkarasu

     /  June 19, 2013

    Weird that the development team decided to hide the option in such a location. Thanks for the information..

  9. Hey there, I think your website might be having browser compatibility
    issues. When I look at your blog site in Opera,
    it looks fine but when opening in Internet Explorer,
    it has some overlapping. I just wanted to give you a
    quick heads up! Other then that, wonderful blog!

  10. Ned

     /  March 3, 2013

    Ingenuious. Thank you very much. This is teriffic.

  11. Hi Suneetha,
    This post really solved big issues for me. Especially after reading on so many other places on the net that it isn’t possible to accomplish dynamic configurations of the Lookup component.

    Thank you
    Magnus Wernersson, Sweden

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: