SSIS Tutorial Part 53-Use Dynamic Query in Lookup Transformation in SSIS Package

SSIS Tutorial Scenario:
We have a big reference table from which we need to extract records in Lookup so we can use against Source File. The table has the data from three regions AS,EU and NA. The files come with the name AS_FileName,EU_FileName and NA_FileName, Instead of extracting all the records for lookup , we want to extract the records according to the region Name. How can we perform that?

What we will learn in this video

How to create SSIS Package from scratch
How to Loop through files by using For-each Loop Container in SSIS Package
How to Read the part of File Name and Save into Variable by using Expressions
How to make use Variable to make Lookup Query dynamic in SSIS Package
How to filter the rows for Lookup Reference Data Set by using Variable Value

Link to the blog post for this video with script if used

Check out our Full Step by Step SQL Server Integration Services(SSIS) Tutorial

[krismaly]

I enjoyed watching this video and recommend others to watch.

Thanks for educating the community and appreciate your efforts

[ejazshahana]

You guys are the best!!! I could not find any better videos on Youtube other than TechBrothers

[aliazad]

Thanks a lot for your time and perfect tutorials. I follow your website which is really so helpful.
Thanks again for sharing such valuable tutorials.

[devexpost]

*The Key Parts:*

1. In the Foreach Loop Editor on the Variable Mapping pane, create a new Variable called User::FileName, mapped to Index=0. As the Foreach Loop iterates over the files in the Directory, it places the name of each current file in this variable name. This is where the dynamic aspect of the package is created and dynamically altered.

2. Within the Data Flow Task, create a Flat File Source and Flat File Connection Manager. Right-Click the Connection Manager and go to Properties->Expressions, open Expressions Editor and for Property “ConnectionString” create an Expression like: This is what creates the dynamically iterative selection of the input files within the loop for the Flat File Source.

3. On the Lookup Transformation Editor Connection pane, create an OLE DB Connection Manager to connect to the master lookup table. Choose “Use results of an SQL Query” and (temporarily) enter the desired SQL statement: SELECT Column1, Column2, Region FROM dbo.MasterLookupTable WHERE Region=’XX’. (This will be over-ridden by an expression in the Data Flow task in the next step.) Then on the Columns pane, map the Available Input Columns to the Available Lookup Columns, and check any desired additional Lookup Columns to “add as new column” to the records being passed out of the Lookup Transformation.

Now, one would think that a dynamic expression for the Lookup’s OLE DB Command would be set up in its own Properties, but not so...

4. Go instead to the Data Flow Task’s Properties->Misc->Expressions, and open the Property Expressions Editor. In its Property list there will appear a property called “[Lookup].[SQLCommand]” (or whatever name you may have changed to for the ‘Lookup’ transformation name). Select it and build the dynamic SQL statement as its Expression... Open the Expression Editor and enter: “SELECT Column1, Column2, Region FROM dbo.MasterLookupTable WHERE Region=’ ”+@[User::RegionCode]+” ’ ”. Hit OK. This is what creates the dynamic selection of only the applicable region records from the master lookup table.

5. In the SSIS main Variables pain, right-click on the Variable and set to “True”.

Best regards.
.

[lius]

Hi, amazing video! thank you for your time on this.

I am trying to do the same thing but for date/time field and filter in the where clause. Any idea how to build the expression? Many thanks.

[accent]

Bhai your videos are really great it solves lots of my issues

[namu]

very nice video, i follow your blogs too. keep up good work.

[danielargueta]

very good video!
a week ago I was looking for how to pass a parameter and be able to insert the result found in an ole database destination, all this using the search but I have not been able to.

could you support me?

[kirankumarkommalapudi]

Do we have this in 2008 version of visual studio..??

[manikantamokidi]

can i use catche in foreachloopcontainer