June 2, 2022 at 6:48 pm
I have a case where i have multiple .zip files in one of folder
example - c:\Test\1234.zip like this we have multiple zip files under Test folder
In each of this zip file we have single folder under that multiple folders and .csv file
example - c:\Test\1234.zip\1234\emp.csv
Is there a way in SSIS to loop through all zip files and retrieve only .csv files?
June 2, 2022 at 8:49 pm
Yes - it can be done but you have to write some C# code in a script task to open the archive, loop through through the results - and extract the files.
See this https://docs.microsoft.com/en-us/dotnet/api/system.io.compression.zipfile.openread?view=net-6.0 as a starting point.
Once you have that CSV file, what are you going to be doing with it?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2022 at 6:10 pm
Once i got all csv files from all the zip files into a folder. i will have to loop through again all csv files and loads the content in that csv file into table
because in each csv file we have some content where i need to extarct and do some more logic to pull .pdf data and send to sftp..
June 3, 2022 at 9:32 pm
I would probably set this up with 2 packages in the project. The first package uses a script task to traverse the folder(s) where the zip archives live - using the code in the link above to extract the CSV files to a specified location. The second package processes any files found in that specified location.
It will take some time to write that code - but once it has been written the other package(s) should be fairly simple. If all CSV files have the exact same format - one package to process that file type. If you have different formats then a separate package for each format - with separate locations where you drop the files.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 5, 2022 at 5:08 am
There's gotta be a way to read the manifest of files from a zip file in SSMS. Almost everything else has that capability.
--Jeff Moden
June 5, 2022 at 2:19 pm
There's gotta be a way to read the manifest of files from a zip file in SSMS. Almost everything else has that capability.
Not sure what you are getting at - or why. The OP needs to look through a directory where there are archived (zip) files - search each zip file for a specific (assuming it is specific) CSV file in the archive, extract that CSV file and load it into the database.
How would SSMS being able to read a zip archive help with that process?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2022 at 5:09 am
Jeff Moden wrote:There's gotta be a way to read the manifest of files from a zip file in SSMS. Almost everything else has that capability.
Not sure what you are getting at - or why. The OP needs to look through a directory where there are archived (zip) files - search each zip file for a specific (assuming it is specific) CSV file in the archive, extract that CSV file and load it into the database.
How would SSMS being able to read a zip archive help with that process?
Apologies... what I'm getting at is that you shouldn't have to expand a zip file to get the list of files names it contains. Most unzip software has a "manifest" command that will return the list of files contained in a zip file.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply