Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Getting a value out of a file to use it in our package

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


 

Published Wednesday, June 15, 2005 10:21 PM by jamie.thomson

Comments

 

Kirk said:

Jamie, nice article guy. It's so cool reading about your solution. I was reading the requirements thinking, hmmmm, how would I do this. Reading down more and I thought, for a small moment as just a casual user, wow that's so cool how you can put the pieces together to do something so easily without writing any code. Good thinking here. Nice to see you start to think outside the box, very cool.
June 20, 2005 1:21 AM
 

katherine gache said:

it helped me much, merci
October 13, 2005 11:46 AM
 

Prakash Srinivasan said:

You are a big dude Mr.Jamie!!!

Helped me a lot.

Prakash Srinivasan
February 23, 2006 7:04 AM
 

SB said:

Thanks!! This was extremely helpful.

May 31, 2007 7:28 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems