I use the Foreach loop container to process files in SSIS. In doing so there are cases where I want to capture the name of the file that the loop is reading.
There are many examples of how to use the Foreach container but I could not readily find how to process the file name into a column of the table. Here's how.
Assuming you've followed the proper steps to capture the file name in the Variable Mappings of your Foreach container you are ready for the next steps.
1. Assuming you have a Data Flow that moves the contents of your file into a table go to the Data Flow tab and drag Derived Column out of your Toolbox.
2. Open the Derived Column Transformation Editor and add a Derived Column Name to the grid. I called mine Source File.
3. In the Expression column drag the Type Cast value (DT_WSTR,<length>) from the function reference or type it in.
4. Locate your variable from the list in the box above and drag it in after the Type Cast.
I found that if you don't Type Cast the variable then I got blanks in my column in the destination table. I also needed to use the SUBSTRING() function to strip the path from the front of the file name. My final Expression value looks like this:
(DT_WSTR,20)SUBSTRING(@[User::MyFileRead],46,20)
My file name column is 20 characters and the actual file name starts in column 46.
You map the source file through the Derived Column object and map that to the destination.
