Loop thorugh variable in ssis

Question

Post reply

Loop thorugh variable in ssis

rayash16

Ten Centuries

Points: 1054
More actions
January 19, 2009 at 8:00 pm

#155147

HI All,
I have a requirement where i have to access the file system where we have data for 9 quarters,and from the name of the file we extract customer and quarter information and put in the fact table.we use variable to store the file name and do the RIGHT and SUBSTRING function to extract that info.
right now we have t manually select the source from the directory and change the value of variable.
i would be obliged if somebody can tell me how to automate this reading the source from the directory and storing that file name in the variable.basically how to loop thorugh the names and store em in variables.
i have idea of using FOR EACH LOOP container ,but i am not sure how to access it with variable.
i hope this description is explains the situation.
second requirement is ,we expect next quarter data to arrive ,we want our package to read the file system and check what all quarter we already loaded.
we know it will involve for each loop container,file system task and variable.
i would be thankful if some body can explain a bit about it.
regards
Ashish

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

1. Create the following in SSIS:

a. User::FileName of type string

b. ForEach Loop Container

c. Any task you need done for each file goes into the container.

2. Set up ForEach Loop Container as follows:

a. Collection: Foreach File Enumerator

b. Enumerator Configuraton Folder : point to your source folder where you files reside

c. Enumerator Configuraton Files: specify which files to read.

d. Variable Mappings - map User::FileName to index 0 (zero)

Now, the User::FileName variable will hold the name of the file that the ForEach Loop container is working on at any given time. You can access that User::FileName variable at any time during that iteration of the foreach loop.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

rayash16 Ten Centuries Points: 1054 More actions · Answer 2

Hi,

Thanks for the quick response.

It worked.Appreciate it.

I have a second question though.

The package is working now ,and storing the values in variable as well.But what happend when new quarter comes in to the directory.

is there any way to put a condition like what ever quarters are already loaded skip them.

thanks in advance

regards

Ashish