How to Navigate through folders to find .zip file

Question

How to Navigate through folders to find .zip file

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
May 25, 2022 at 1:54 pm

#4039059

Is there any script task in SSIS to find the zip files under each sub folders?
We have folder inside there are lot of sub folders and we dont know in which folder .zip file exists
May 31, 2022 at 11:20 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

What is the end goal here? You could, for example, easily use a Foreach Loop Container to go through the folders recursive and filter to zip files. Why does it need to be a script task? What are you doing with the file afterwards?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

Here is requirement

i have directory and i know the path where it creates folder

D:/Employees/

Under this employees folder we will have so many sub folders, and its not constant we dont know what is the exact folder name to look into employees folder for the zip file.

Also inside each zip file we will have .csv file. My main gole to from this directory navigate through each and every folder and get those csv files and keep in a destination path.

for example - path will be

D:/Employees/EmdData202205250194741/Workspace/2021/June/GDFyetr/SFG99e/CWevvg.zip

Above is the sample one. but after D:/Employees/EmdData202205250194741/Workspace

folder names keeps on changing. we dont know in which folder we have zip file and inside zip where is csv file.

Thom A SSC Guru Points: 99130 More actions · Answer 3

Then it seems that what you want, as I mentioned, is a Foreach Loop container; that can loop on the contents (including subfolders) of D:/Employees and you can filter to files ending in *.zip. Then you can do what ever you need with the file iteratively with each zip file the Foreach Loop Container finds.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Doesn't SSIS allow you to run a CMD Task where you could issue the following simple DIR command and capture the output to a table? It'll give you the full path to each and every ZIP file that it finds.

DIR "D:\Employees\*.zip" /s /b

--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)

Thom A SSC Guru Points: 99130 More actions · Answer 5

Jeff Moden wrote:

Doesn't SSIS allow you to run a CMD Task where you could issue the following simple DIR command and capture the output to a table? It'll give you the full path to each and every ZIP file that it finds.
DIR "D:\Employees\*.zip" /s /b

It does, though if you were to go that method I would suggest Powershell over Command Prompt. Though using a For Each Loop, which explicitly has the ability to loop through files (in a directory (recursively) and can filter to files based on a pattern seems like the easiest way to do the task inside SSIS.

If you use CMD/Powershell, you'd still need to then iterate through the data set it passes back; so might as well do the task as you iterate through the files.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Thanks, Thom. It sounds like you've had some good experiences with SSIS.

A little bit off topic, if you good folks don't mind... I am curious about good experiences in SSIS (and, no... not being ironic there... they made the tool for a reason). I've had a couple of people tell me that it was an arduous task to migrate SSIS packages when they migrated from one version of SQL Server to the next, especially if the migration was to new hardware at the same time.

When I say "arduous", that was the description according to a good friend of mine that went through such a migration and she said it took her days to migrate "hundreds" of SSIS packages. This is a person that also adores the functionality of SSIS and so it's not like she's anti-SSIS or anything like that.

I've never had to do such a migration and so I'm quite curious on your thoughts on that subject.

--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)

Thom A SSC Guru Points: 99130 More actions · Answer 7

Jeff Moden wrote:

A little bit off topic, if you good folks don't mind... I am curious about good experiences in SSIS (and, no... not being ironic there... they made the tool for a reason). I've had a couple of people tell me that it was an arduous task to migrate SSIS packages when they migrated from one version of SQL Server to the next, especially if the migration was to new hardware at the same time.

When we migrated from 2008 to 2012 is was arduous, however, there is a reason for that; SSISDB was introduced which was a brand new deployment method. It's (in my opinion) a significant improvement on the old MSDB and File System deployment methods, but moving from one deployment method to another is also involved.

As for moving after that, we're actually in the process of migrating at the moment, from 2012 to 2019. Moving our Development and Testing environments were honestly trivial; I just had to create the SSISDB (get annoyed that SSISDB ignored the model database and created it in the wrong recovery model and lacked certain custom roles/users we want across the board), set up the keys, and then just deploy. Took me, 10 minutes?

I wouldn't say I'm an advocate for SSIS; it has an awkward learning curve and it's very clunky at times. I wouldn't say I've had bad experiences with SSIS, but I wouldn't say it's specifically been "good"; I'm pretty neutral to it. I might be more positive when we're on 2019 across the board, and I don't have to redploy an entire project every time I make a change to a single package. It is, however, a better option than Powershell on SQL Server 2012 though; the fact that SQLPS on 2012 runs Powershell 2.0 is a significant limiting factor on some of the things I have wanted to do in the past.

I do make a lot of use of it though; we have several ETL projects that it uses. Part of that reason, however, might be because of the 3rd party application we use, and the lack of control we have over it; forcing ETL process that should be application based to be migrated elsewhere, and often that ends up in SQL Server's SSIS tool. It does those jobs well, provided that staff members follow instructions properly. 🙂 (the amount of times I see Excel files with different headers, or are passworded (which obviously SSIS can't read) and I get an email saying "It's not working" infuriates me. Especially when the message they get explicitly asked them to check that first.)

This reply was modified 2 years, 5 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 8

On a side note, I am also putting forward tomorrow we actually consider delaying the migration (as we're so far behind schedule) and wait for 2022 at this point. We aren't going to be ready before the the EoL of 2012, as the new server room has only just been finished and we don't have the new hardware in place, so seems we might as well bite the bullet for a while and use it for a while even after support ends and then get 2022; at least we get 10 years of support rather than 7 (or 5 instead of 2 if we're talking full support).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

Thanks for the feedback, Thom. I appreciate it.

--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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

mcfarlandparkway wrote:

Here is requirement
i have directory and i know the path where it creates folder
D:/Employees/
Under this employees folder we will have so many sub folders, and its not constant we dont know what is the exact folder name to look into employees folder for the zip file.
Also inside each zip file we will have .csv file. My main gole to from this directory navigate through each and every folder and get those csv files and keep in a destination path.
for example - path will be
D:/Employees/EmdData202205250194741/Workspace/2021/June/GDFyetr/SFG99e/CWevvg.zip
Above is the sample one. but after D:/Employees/EmdData202205250194741/Workspace
folder names keeps on changing. we dont know in which folder we have zip file and inside zip where is csv file.

This still doesn't really answer the question asked - what do you need to do with these files once you find them? Are you loading them into a database, moving them to another folder - something else?

It seems to me that you are looking for the zip files - once found you want to extract the zip - and then copy/move the CSV files found to some other location. If that is the case, then a script task is the correct component to use as long as you are using a high enough version of .NET for that component (need this so you can use native C# code to extract the zip files).

Further to that - if you are not doing anything other than moving those files, then maybe using SSIS isn't the right process. That would be a lot of overhead just to scan folders, extract zip files and copy/move the extracted files. All of that can be done just as easily using Powershell and would be more efficient.

So - what is the goal once you have those files?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs