*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: @[User::InputFilePath]+”\\”+@[User::FileName] 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 User::RegionCode->Properties->[Misc]->EvaluateAsExpression and set to “True”. Best regards. .
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.
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?
*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: @[User::InputFilePath]+”\\”+@[User::FileName] 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 User::RegionCode->Properties->[Misc]->EvaluateAsExpression and set to “True”.
Best regards.
.
Great work. 👍🏼
I enjoyed watching this video and recommend others to watch.
Thanks for educating the community and appreciate your efforts
You guys are the best!!! I could not find any better videos on UA-cam other than TechBrothers
+ejazshahana Thanks for kind words dear! Glad you liked our work!
Its True
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.
Thank you dear for liking our effort and thanks for taking time to write nice comments.
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.
Bhai your videos are really great it solves lots of my issues
+Abhishek Patil Glad to hear that brother. All the best and thanks for watching!
very nice video, i follow your blogs too. keep up good work.
Thank you for kind words. Glad to hear that you liked our effort.
Do we have this in 2008 version of visual studio..??
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?
Thank you for videos!! very helpful!!
can i use catche in foreachloopcontainer
the video is no clarity
not about subject