Windows folder names to excel output file

Question

I have a base shared folder that holds thousands of sub folders where ssis packages reside. Is there a easy way to create an excel or text file with the sub folder names?

Can this information be queried from windows somehow?

Ideally I would like to filter the output by a wild card filter. Something like listing all folders where the name starts with LWD_INT...

Answer 1

The quick and dirty way of solving this is use a directory search command, dir and then indicate you want to search recursively through subfolders /s and then return the results in the brief /b format.

dir /s /b \\appsqlvs\IntegrationTST\INT_LawsonPrograms*.dtsx

Since this works, you can then either redirect the output with the > operator (>> redirects with append) to a file like

dir /s /b \\appsqlvs\IntegrationTST\INT_LawsonPrograms*.dtsx > C:\ssisdata\LawsonPackages.txt

and then parse it as you will.

The other approach you can do and to satisfy the SSIS tag, is do the first command in an Execute Process Task and wire up the results to a string Variable. Then you can parse that as you wish. Only real difference is the first approach is going to write to disk and then you can "do" something with it while the Execute Process Task skips the file intermediary.

If you go the SSIS route, I'd probably actually skip the Execute Process Task and use a Script Task to generate this data set as there are probably specific things you are looking for. The Directory library is a good starting point, specifically EnumerateFiles