Solved

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

Posted on 2014-07-27
7
3,480 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:AmitJain001
  • 4
  • 3
7 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 40223516
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
 

Author Comment

by:AmitJain001
ID: 40226249
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40226259
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:AmitJain001
ID: 40226319
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40226338
Please remove double quotes ( " ) at the beginning and end of folder path from below line and try:
<ConfiguredValue>"E:\Folder1"</ConfiguredValue>
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40226359
And for  <ConfiguredValue>"D:\Folder2"</ConfiguredValue> too. Everything between <ConfigureValue> tags becomes part of the folder path.
0
 

Author Closing Comment

by:AmitJain001
ID: 40226495
Thanks Harish,
 It solved my problem.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now