SSIS retrieving current folder within a foreach loop traversing subfolders

I use SSIS to read .txt files in input and execute my business logic over them saving the output resul开发者_如何学Cts in a file whose name is the same as the current inpout file (file name dynamically stored in a variable).

When all the files are stored in the same folder, I have no problem accessing them since I use the following expression for the flat file connection string in the data flow: "path" + @[User::inputFileName] + ".txt"

Now I have to process a folder with subfolders (I set traverse subfolders in the foreach loop) and I have some issues with the flat file connection string since I cannot use a wildcard like: my path\\subfolder*" + @[User::inputFileName] + ".txt" where every subfolder has same name and changes only the last portion of the name.

How can I save the current subfolder name in a variable so that I can use it in the following way? "path\\"+ @[User::currentSubFolder] +"\\" + @[User::inputFileName] + ".txt"

I was able to solve my issue, therefore I write here my solution in the case someone else would be in the same situation.

I used a script transformation block before my foreach loop. From it I can retrieve the current full path (used afterwards in the Flat File connection string) and the input file name without extension to be used as output file name containing the results of the SSIS scripts.

In order to keep the values of interests I used 2 variables: one for the file name and one for the path.

Here the script code:

Public Sub Main()

    'Variable Index 0 => FileName
    'Variable Index 1 => filePath

    Dim fullPath As String = Dts.Variables.Item(1).Value.ToString
    Dim fileName As String = Path.GetFileName(fullPath)
    fileName = fileName.Substring(0, fileName.Length - 4) 

    Dts.Variables.Item(0).Value = fileName
    Dim x As String = Dts.Variables.Item(0).Value.ToString

    Dts.TaskResult = Dts.Results.Success
End Sub