asked on

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.

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.

ASKER CERTIFIED SOLUTION

Harish Varghese

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AmitJain001

ASKER

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.

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.

I am not able to attach the source due to unsupport file type like dtsx,sou etc.

Harish Varghese

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?

AmitJain001

ASKER

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>

Harish Varghese

Please remove double quotes ( " ) at the beginning and end of folder path from below line and try:
<ConfiguredValue>"E:\Folder1"</ConfiguredValue>

Harish Varghese

And for <ConfiguredValue>"D:\Folder2"</ConfiguredValue> too. Everything between <ConfigureValue> tags becomes part of the folder path.

AmitJain001

ASKER

Thanks Harish,
It solved my problem.