Solved

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

Posted on 2014-07-27
7
3,544 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

785 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