Get subfolder name into variable

Question

Post reply

Get subfolder name into variable

lduvall

SSCarpal Tunnel

Points: 4869
More actions
December 10, 2009 at 1:28 pm

#140607

I'm trying to use SSIS to import a variety of text files from one main folder with several sub-folders. I want to use the sub-folder name as an input variable to a stored proc. I know how to set up the "For Each Loop" so that it will "traverse subfolders" within my collection, but I don't know how to obtain the actual subfolder name as it's going through them. This is the directory structure:
Course Section
BIO 1510 01
2009121001.txt
2009120901.txt
BIO 1510 02
2009121001.txt
2009120901.txt
I have the For Each Loop set with the "Course Section" folder as the collection starting point. I have it set up to pull all the .txt files beneath Course Section in a looping fashion. I am able to get the file name and use it as an input variable to my stored proc, but I want to be able to use the Sub-Folder name, too. Help??? I hope I explained well enough. Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

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

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 1

[font="Comic Sans MS"]

Hello -

I am able to get the file name and use it as an input variable to my stored proc...

Aren't you getting the complete UNC path of the filename? like : in your case it would be

Course Section
BIO 1510 01
2009121001.txt
2009120901.txt
BIO 1510 02
2009121001.txt
2009120901.txt

"\\somepath\Course Section\BIO 1510 01\2009121001.txt

I guess you are storing each filename into a variable. If this is the case you can use string operator get the folder path.

let me know if this makes sense to you ..

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 2

I'm not getting the full UNC path. I'm just getting the name & extension. I've got the radio button checked that says "Fully qualified" but it's not bringing it back. I would definitely love to do the substring thing if I could get the full UNC path. Any ideas??

sabyasm SSCarpal Tunnel Points: 4324 More actions · Answer 3

lduvall (12/10/2009)
I'm not getting the full UNC path. I'm just getting the name & extension. I've got the radio button checked that says "Fully qualified" but it's not bringing it back. I would definitely love to do the substring thing if I could get the full UNC path. Any ideas??

[font="Comic Sans MS"]

Little surprised here. Can you see the link below and check if you are doing anything differently?

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

You should get fileJustFound to be the file with full UNC path ..

[/font]

[font="Comic Sans MS"]--
Sabya[/font]

Tim Mitchell SSCoach Points: 15684 More actions · Answer 4

I agree with Sabya - if you're using the fully qualified name, you should get everything including from the drive/folder through the extension in your variable.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 5

I got it! The problem was that I was modifying an existing package in order to use the subfolder name. Originally, I needed only the filename & extension. When I switched the radio button & saved the package, I expected it to change to use UNC. What I failed to do was to change the initial variable value. (The one you set so that it has something to look at before it begins to loop.) When I changed that, it started working.

Thanks for your help!

Tim Mitchell SSCoach Points: 15684 More actions · Answer 6

Good deal, thanks for the follow-up.

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices