Looping through a directory for sub-directories' names

Question

Post reply

Looping through a directory for sub-directories' names

ktlady

SSCommitted

Points: 1509
More actions
November 4, 2009 at 6:03 pm

#136779

I have a directory named e.g. dir1
under which there could be one or more datecoded directories:
20091101_12
20091102_03
abc
xyz
I'd like to get the return of list of those date-coded subdirectories, i.e.
20091101_12
20091102_03
so that I can go under them and get files, perform other actions, etc...
How do I do that? I tried Foreach Loop container using Foreach File Enumerator. However, that gets only the Files, not the directories for me.
Thanks for any help in advance!

Viewing 15 posts - 1 through 15 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

Build a Temp table to accept results from the following (using Insert/Exec) and then query the Temp table...

EXEC dbo.xp_DirTree 'file path or UNC',1,1

Works in 2k, 2k5, and 2k8. Don't know if it will work in 2k10 because it's undocumented.

BTW, a lot of people know about xp_DirTree but, if you do a Google search, most don't know about the third parameter I used. The second parameter (first "1") controls how many levels to "look down" with the current level being level 1. The 3rd parameter (second "1") tells xp_DirTree to list files and directories and to include a flag on files in the output.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ktlady SSCommitted Points: 1509 More actions · Answer 2

Is there any way to do the sub-directory search inside SSIS package without having to involve database? Thanks!

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

ktlady (11/6/2009)
Is there any way to do the sub-directory search inside SSIS package without having to involve database? Thanks!

Probably, but I don't know because I don't use SSIS. I've always done all my ETL within some high speed T-SQL, BULK INSERT and BCP being my favorites. Sorry I can't be of more help on SSIS but this may serve as a "bump" for your post... hopefully, someone will see it and come to the rescue here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 4

Anyone interested in SSIS should be familiar with the name Jamie Thompson.

See http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx for this question (notice the Enumerate Subfolders box), but also check out the wealth of great stuff on that blog, and also his new blog over at sqlblog.com

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 5

Thanks, Paul.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ktlady SSCommitted Points: 1509 More actions · Answer 6

I understand how to get files using the "Foreach file Enumerator".

My question here is "I'd like to get the return of list of those date-coded subdirectories, i.e.

20091101_12

20091102_03"

How do I get the list of subdirectories (instead of files)

Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 7

I'm sorry - I misread your question.

There isn't a built-in way to enumerate directories by themselves (just files down a recursive tree) but you could install the Microsoft ForEachDirectory sample, which adds a directory enumerator to the for each loop container.

If you didn't install the samples when you installed SSIS, you can get it from http://www.codeplex.com/MSFTISProdSamples

Directory paths can be read into SSIS in other ways (configuration files, xml files, from the database, using a script component, the execute process task...and so on) but the sample is a good one.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 8

In case you decide to use a script component, you just need to create a one-liner along these lines:

Dts.Variables[0].Value = System.IO.Directory.GetDirectories(@"C:\Temp");

Give that component a variable of Object type to write to, and you can then use that same variable as the collection variable in a ForEach Loop Container: using the For Each From Variable type.

The one line of code puts a .NET string array of directories into the variable, which the For Each From Variable component can iterate over directly (since arrays implement IEnumerable).

Of course you can make the one line bit more sophisticated by specifying a search pattern or deciding whether to search for directories recursively or not...or anything else you can do in .NET

I wrote the example in C#, but I believe the VB syntax would be very similar.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ktlady SSCommitted Points: 1509 More actions · Answer 9

Paul, Thanks a lot for your script! I'd like to use that to get my sub-directory names and then use Foreach container to loop through.

Would you please elaborate the C# script a little bit more to constrain the sub-directories to only include those names start with "20xx"? Basically, i am not very good at scripting and if you could provide the extra line of code, i'd really appreciate it. Thanks again!

Paul White SSC Guru Points: 150467 More actions · Answer 10

Paul White (11/9/2009)
Dts.Variables[0].Value = System.IO.Directory.GetDirectories(@"C:\Temp", "20??");

http://msdn.microsoft.com/en-us/library/system.io.directory.getdirectories.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ktlady SSCommitted Points: 1509 More actions · Answer 11

ktlady

SSCommitted

Points: 1509

November 9, 2009 at 5:31 pm

#1077044

Great, thanks!

irfanshaukat999 SSC Enthusiast Points: 123 More actions · Answer 12

Hello Jeff

Your Single line of code has helped me alot!

Actually what I am trying to do is loop through all the subdirectories under a root folder and load the files in the SQL Server table.

Till now I am able to get the files and load them in tables just form the root/main folder; not able to get the subdirectories and files.

Here is the part of the code I am using for this:

Set @PreparedBatch =

'DECLARE @XML XML

SELECT @XML = CAST(BulkColumn AS XML)

FROM OPENROWSET(BULK ''?'', SINGLE_BLOB) AS x

INSERT test.dbo.abc (data)

SELECT @XML;';

Set @Path = 'C:\Root\'

Set @param = 'dir /B ' + @Path + '*.txt'

The scenario is: if there are further subdirectories (dir1,dir2,dir3)under the root and each of them is containing multiple files. Then what should be the code/procedure for doing this.

I was event unable to get the directory structure but Your code made it possible for me. Please guide me further.

Best Wishes

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 13

Can you use xp_CmdShell or sp_OACreate?

Also, considering the good information others have posted about how to do this with SSIS, are you sure you want a T-SQL only solution?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

irfanshaukat999 SSC Enthusiast Points: 123 More actions · Answer 14

Hi

Many thakns for your reply:)

yes I can use xp_CmdShell or sp_OACreate.

No I am not restricted to T-SQL solution. Actually I dnt know much about SSIS. I did serach about it and explored; it is not installed on the mechine which I am using. If I insall SSIS through the SQL Server; will it require the system reboot?

Please guide me and suggest me any possible solution.

Thank You.