|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Tuesday, November 17, 2009 5:09 PM
Points: 39,
Visits: 79
|
|
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!
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 18,300,
Visits: 12,321
|
|
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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, November 17, 2009 5:09 PM
Points: 39,
Visits: 79
|
|
| Is there any way to do the sub-directory search inside SSIS package without having to involve database? Thanks!
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 18,300,
Visits: 12,321
|
|
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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 18,300,
Visits: 12,321
|
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, November 17, 2009 5:09 PM
Points: 39,
Visits: 79
|
|
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!
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
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
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
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.
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, November 17, 2009 5:09 PM
Points: 39,
Visits: 79
|
|
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!
|
|
|
|
