How to Navigate through folders to find .zip file

  • 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

     

  • 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

  • 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.

     

     

     

     

     

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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 1 month ago by  Thom A.

    Thom~

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

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • This was removed by the editor as SPAM

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

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