Solved

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

Posted on 2014-07-27
7
3,864 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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