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 @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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 @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1586325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse