Need help to configure ForEach Loop container to use ForEach Item Enumerator in SSIS 2008R2

Hi Experts,

I am trying to build a simple SSIS package that loops through subfolders within a Folder. Now there can be many ways of doing that but my intention is to use ForEach Loop Container that uses ForEach Item Enumerator.

Let me explain how !

Below is my simple package. Please note the two variables, objFolder and srcFilePath.

SSIS-Package-Outline.png
The first step is to access the Source Folder (Folder specified by srcFilePath) and create a collection of SubFolders. This is done within the Script folder using this simple statement:

// Step 1 - Get the Source Folder Name 
_strFilePathName = (String)Dts.Variables["SrcFilePath"].Value;
Dts.Variables["objFolder"].Value = Directory.GetDirectories(_strFilePathName, "*", SearchOption.AllDirectories);

Open in new window


Till here there's no issue.

Now I want to use a ForEach Loop Container that uses ForEach Item Enumerator and iterate through the objFolder collection. I have spent many hours reading through material on the Internet but I haven't got a single link that tells me how to use the collection variable to be assigned within the ForEach container. Can someone please point me in the correct direction. Given below is the screenshot of the foreach I need to configure.

ForEach-Container.png
AmitJain001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harish VargheseProject LeaderCommented:
Foreach Item Enumerator does not accept any input from outside the container. It is used only on collections defined inside the Foreach Container. For iterating through files in a directory, the best option is to use "Foreach File Enumerator" by providing source folder as an expression.
But if you are particular that you need to get the files list outside the container and pass it using your objFolder variable, then you can use "Foreach From Variable Enumerator". I am having problems uploading images here. So here are the detailed instructions for both options (it may be useful for others also):

OPTION 1: Use Foreach File Enumerator.
1. Double click on Foreach Loop Container to invoke Foreach Loop Editor. (You dont need the first script task that you used to populate objFolder variable, in this case).
2. Select "Collections" on the left side, and select "Foreach File Enumerator" for Enumerator on the right.
3. Click on Expressions (below collections) and click on the three dots (...) to invoke Property Expressions Editor.
4. Choose "Directory" from the dropdown below "Property" column and provide expression as "@[User::SrcFilePath]". You may also use the Expression builder by clicking on the button on the row.
5. Click "OK" to close Property Expression Editor window and return to Foreach Loop Editor.
6. Check the "Traverse Subfolders" checkbox down the bottom of Foreach Loop Editor box if you want to scan through sub folders. Choose appropriate "Retrieve File name" option as required.
7. Click on Variable Mappings on the left, click on the dropdown under Variable on the right and select <New Variable...>.
8. Give a name for the variable (say, filenameInLoop), and click OK to close the dialog.
9. Click OK to close the dialog.
10. Place a Script task inside the Foreach Loop Container.
11. Double click the script task to open Script Task Editor window.
12. Select Script menu on the left and against Readonly Variables on the right, provide the variable you created in step 8 above. (User::filenameInLoop).
13. Copy below line and paste in Main function in the script editor:
MessageBox.Show(Dts.Variables["filenameInLoop"].Value.ToString(), "Current filename");

Open in new window

14. Save and close the script editor.
15. Click OK to close the Script Task Editor.
16. Execute the package to see the filenames under the selected folder.

OPTION 2: Use Foreach From Variable Enumerator.
1. Double click on Foreach Loop Container to invoke Foreach Loop Editor.
2. Select "Collections" on the left side, and select "Foreach From Variable Enumerator" for Enumerator on the right.
3. Choose your object variable (User::objFolder) from the dropdown below "Variable" in Enumerator Configuration" section below.
4. Click on Variable Mappings on the left, click on the dropdown under Variable on the right and select <New Variable...>.
5. Give a name for the variable (say, filenameInLoop), and click OK to close the dialog.
6. Click OK to close the dialog.
7. Place a Script task inside the Foreach Loop Container.
8. Double click the script task to open Script Task Editor window.
9. Select Script menu on the left and against Readonly Variables on the right, provide the variable you created in step 8 above. (User::filenameInLoop).
10. Copy below line and paste in Main function in the script editor:
MessageBox.Show(Dts.Variables["filenameInLoop"].Value.ToString(), "Current filename");

Open in new window

11. Save and close the script editor.
12. Click OK to close the Script Task Editor.
13. Execute the package to see the filenames under the selected folder.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AmitJain001Author Commented:
Hi Harish,
I have tried both ways you explained in  
first solution:
     For each loop container is excuting one time but the script task within the container is neither being executed nor giving any error.
SSIS-001.JPG
Second Solution:
    Its Giving Error   "Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Illegal characters in path." in First script task it self.


SSIS-002.JPGI am not able to attach the source due to unsupport file type like dtsx,sou etc.
0
Harish VargheseProject LeaderCommented:
I see a warning message "The path is empty" in first image.
If you are using an expression for Directory name (SrcFilePath), then verify the contents of your folder.

For second attempt, you can put a break point inside your script in script task to debug and see what is the issue. What is the path that you are using?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AmitJain001Author Commented:
Harish,

I had created some text and xls files hence its not empty but package is not recognising them.
In second its showing "illegal character in path" and the variable is displaying "\"E:\\Folder\"" where as I have defined Following Configurations in Configuration File.

<?xml version="1.0"?>
<DTSConfiguration>
      <DTSConfigurationHeading>
            <DTSConfigurationFileInfo GeneratedBy="SystemName\Administrator" GeneratedFromPackageName="Package2" GeneratedFromPackageID="{23F489F0-3EB7-4EC5-8AC3-7B9577BD4212}" GeneratedDate="7/29/2014 2:08:39 PM"/>
      </DTSConfigurationHeading>
      <Configuration ConfiguredType="Property" Path="\Package.Variables[User::StrSrcFilePath].Properties[Value]" ValueType="String">
            <ConfiguredValue>"E:\Folder1"</ConfiguredValue>
      </Configuration>
      <Configuration ConfiguredType="Property" Path="\Package.Variables[User::StrStageFilePath].Properties[Value]" ValueType="String">
            <ConfiguredValue>"D:\Folder2"</ConfiguredValue>
      </Configuration>
</DTSConfiguration>
0
Harish VargheseProject LeaderCommented:
Please remove double quotes ( " ) at the beginning and end of folder path from below line and try:
<ConfiguredValue>"E:\Folder1"</ConfiguredValue>
0
Harish VargheseProject LeaderCommented:
And for  <ConfiguredValue>"D:\Folder2"</ConfiguredValue> too. Everything between <ConfigureValue> tags becomes part of the folder path.
0
AmitJain001Author Commented:
Thanks Harish,
 It solved my problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.