Someone recently asked on a newsgroup how they could grab hold of a value from a given field in the last row of a file. For example, in the file depicted below we would like to grab the value "Posh".
I figured this might be a fairly common requirement so figured I'd put something together to show how to do this. There's probably a number of ways to accomplish this, but this is the way I would do it.
The solution to this is two-fold:
1) Populate a recordset with the contents of the file
2) Iterate over the recordset using a Foreach loop, each time capturing the "Name" field into a string variable
Once the Foreach loop has finished iterating the string variable will contain the value "Posh".
Here's how it works.
1. Define a variable to hold the recordset and a variable to hold the value extracted from the recordset
2. Populate the recordset variable using the Recordset Destination component in a data-flow
3. Define a Foreach loop with the "Foreach ADO Enumerator" and set the source variable to be the recordset variable we have just populated
4. In the Foreach loop, populate the string variable with a value from the current enumerated record.
5. For demo-ing, stick on a script task that outputs the current value of our string variable.
Here's our simple control-flow. Note that there is nothing actually inside the foreach loop - its not needed.
Executing the package results in the following message box.
And its as easy as that! All we've done is capture the contents of the file into memory and then iterated over it until we get to the record we want.
If you want to try this, download the package and source file from here.
-Jamie