Solved

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

Posted on 2014-07-27
7
3,425 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

11 Experts available now in Live!

Get 1:1 Help Now