Post reply

Retrieving SubFolder Names in SSIS

  • June 26, 2014 at 1:31 am

    #292685

    Hi All,

    I have a requirement to process excel files present in certain folder. Below is the Folder structure:

    -Main Folder

    Under Main Folder,

    --EmpA--File1,File2

    --EmpB--File1,File2

    --EmpC--File1

    To process all the files in Main Folder, I can use Foreach file enumerator and selecting the checkbox 'traverse subfolders' in For each loop container.

    However, I need to read the SubFolder Names (EmpA,EmpB,EmpC) to lookup on SQL table to retrieve the EmpID.

    Below is the Table structure:

    EmpID EmpName

    1 EmpA

    2 EmpB

    3 EmpC

    It would be of great help if you could share your experience in case you ever came across similar requirement.

    Regards,

    Maninder

  • June 26, 2014 at 1:51 am

    #1724931

    You can capture the current iteration's file path in a user variable.

    Have a look at the Variable Mappings section here[/url] and let us know if you need more info.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 26, 2014 at 3:37 am

    #1724977

    Thanks Phil.

    Creating user variable and Using Substring Function (to retrieve sub folder name) in Derived column transformation worked for me 🙂

    Cheers!

    Maninder

  • June 26, 2014 at 3:42 am

    #1724979

    Great, well done.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply