November 15, 2009 at 8:52 pm
I want to run a file system task in for each loop container. I am defining a variable in for each loop container and mapping that variable in the source property of my file system task but it says that the variable is EMPTY.
Hows that?
November 16, 2009 at 1:59 am
Here are some things you can check:
1. Check the Folder property of loop, whether it is pointing to correct folder.
2. check the Files property and see whether it using the right file name pattern.
Following are the steps one should follow for using variable file names in for each loop:
1. Define the variable at package/loop scope.
2. Set the for each loop Folder property to the correct folder.
3. Set the for each loop Files property to the correct file name pattern (like *xyz.txt).
4. In the "Variable Mappings" tab of for each loop select the defined variable.
5. Then use that variable inside the loop.
-Vikas Bindra
November 16, 2009 at 5:58 pm
Hello Kunaal - I had a similar problem. To resolve it, I just added a default value to the variable in the variables box. I just set the value of the variable to the letter 'x'. Then the ssis package ran. I do not know if there is a better way around this or if it's a violation of some best practice, but it worked. Just make sure that your code actually sets the variable some where in your code. Hope this helps.
November 17, 2009 at 7:46 pm
Hello guys I am really sorry for not being able to get back to you on time.
So here is what I have analysed so far:
1. If you mean Directory property when you say folder property then yes it is pointing to the right folder.
2. Basically I have used *.* in the files section and what I am doing with it is basically using this information in the file execute process which is inside the For each loop container to delete all the files inside this folder irrespective of the types. ( I have also checked traverse subfolders too option.).
I have followed the following steps:
1. Defined the variable in for each loop container.
2. Defined the right folder in the folder property.
3.File name pattern is *.*.
4. In variable mapping, I have selected ' new variable' option. and kept the value field empty considering that the value which i have selected in collections tab will directly be bound to it.
5. I have used the variable in file system task..
Now @ MMADDOX
Hmmmmmm, well I am a new bie too an into my understanding, I think the what we feed in the value field of the variable would be the default value for that particular variable. If I assign 'x' then all the tasks will have default path as 'x', right?
I am not sure if I am right in understanding what you are saying. But I have tried putting X and absolute path of the folder both in value field of the variable but did not help.
I am not sure what is going wrong.
November 18, 2009 at 1:05 am
Hi Kunal,
Your steps looks good.
But the below 2 statements are a bit confusing.
1. Defined the variable in for each loop container.
4. In variable mapping, I have selected ' new variable' option.
By the above 2 statements it seems that you have 2 different variables defined. but you are using one of these.
Anyways, Do you have multiple foreach loops using the same variable name and running in parallel?
4. In variable mapping, I have selected ' new variable' option. and kept the value field empty considering that the value which i have selected in collections tab will directly be bound to it.
Yes you are right here, the loop will pick up files one by one and will assign the file locations to the variable.
2. Basically I have used *.* in the files section and what I am doing with it is basically using this information in the file execute process which is inside the For each loop container to delete all the files inside this folder irrespective of the types. ( I have also checked traverse subfolders too option.).
When you want to delete everything from a folder including the subfolders why don't you use the File System Task with Delete directory content option?
-Vikas Bindra
November 18, 2009 at 1:10 am
Hey Kunal,
Can you provide us the screen shot of for each loop and the file system task.
-Vikas Bindra
November 19, 2009 at 12:30 am
Hi Vikas,
Hmmmm I am not sure how to say but it turned out that all the fuss was about a silly mistake made by me. I had actually selected "Delete directory" option instead of delete file option.
That was really a silly mistake from me.
But if you don't mind I still want to clear one confusion:
As far as my understanding is concerned, I think that variables are mapped to whatever folders are specified in Collections tab. So that is an implicit mapping.
Now, what if after a variable is defined in for each loop container, I go to Variables window and assign an explicit default value. Which one would be considered?
I am not sure how to trace variables in SSIS so it is just I am wondering about variables.
November 19, 2009 at 1:49 am
But if you don't mind I still want to clear one confusion:
As far as my understanding is concerned, I think that variables are mapped to whatever folders are specified in Collections tab. So that is an implicit mapping.
Now, what if after a variable is defined in for each loop container, I go to Variables window and assign an explicit default value. Which one would be considered?
The value assigned in the Variable window will be overwritten by the one from the value picked up by the for each loop. Think of the default value(assigned in variable window) and changing the value later in the program by assigning a new value (in the loop). The later will be the new value.
I am not sure how to trace variables in SSIS so it is just I am wondering about variables.
You can trace the variable by having breakpoints in a task, there are different options available, just right click on the task and check the Edit Breakpoint option. Once you hit the break point open the "Locals" (Debug-> Windows->Locals)window and you will find all the variable with their current value in that window.
-Vikas Bindra
November 19, 2009 at 2:40 am
When working with variables it can be useful to set the Delay Validation option to True on components as they then don't need values until run time. Otherwise set default values in your variables which will get replaced at run time.
November 19, 2009 at 12:33 pm
Yes - the Delay Validation property here needs to be set true. This will prevent the compiler to derive the value of the variable (here path/filename etc) till runtime and you are good to go with.
[font="Comic Sans MS"]--
Sabya[/font]
November 19, 2009 at 12:37 pm
Oh yes, that was a great knowledge exchange. You guys are right, I have had problems before when I tried to use varibales withour setting delay validation.
Thank you very much for your help and concern guys :).
November 19, 2009 at 12:38 pm
P Jones (11/19/2009)
When working with variables it can be useful to set the Delay Validation option to True on components as they then don't need values until run time. Otherwise set default values in your variables which will get replaced at run time.
Thank P! This is exactly what I needed to know. But I just did not know that I needed to know it. Fixed my problem.
November 19, 2009 at 12:39 pm
Is there any way that I can mark you people's replies as 'answer'? I don't see an option like that here...
November 19, 2009 at 12:43 pm
Thank P! This is exactly what I needed to know. But I just did not know that I needed to know it. Fixed my problem.
Maddox, I think that was the reason why, when you did put an 'x' then it would not bother you. Because it was getting its share of default value and you did not need delay validation anymore.
I htink so....
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply