Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieving SubFolder Names in SSIS Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 1:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:22 AM
Points: 13, Visits: 79
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
Post #1586260
Posted Thursday, June 26, 2014 1:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 5,236, Visits: 12,149
You can capture the current iteration's file path in a user variable.

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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1586265
Posted Thursday, June 26, 2014 3:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:22 AM
Points: 13, Visits: 79
Thanks Phil.

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

Cheers!
Maninder
Post #1586323
Posted Thursday, June 26, 2014 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 5,236, Visits: 12,149
Great, well done.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1586325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse