Unzipping/Importing Multiple Files

Question

Unzipping/Importing Multiple Files

pop

SSCrazy

Points: 2130
More actions
August 20, 2007 at 2:19 pm

#178133

I need to search for all files in a directory of type xyz*.zip, unzip the .csv file to a directory, import the file, move the zip file to another directory, and delete the extracted file. Can I use the For Each Loop Container and Execute Process Task? Has anybody done this and can recommend a method? Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Without out going into any details, yes you can. You would also use the File System Task in the For Each Loop Container for moving and deleting the files.

pop SSCrazy Points: 2130 More actions · Answer 2

Appreciate the reply.

I'm trying to unzip each file in a directory to another location but can't seem to get it to work. It's the free zip tool 7zip. I'm entering the following:

Executable: C:\Program Files\7-Zip\7z.exe

Arguements: e @[User::FileName] -o:c\dep\unzip

Working Directory: C:\PMOFTP\

the e is for extract and -o outputs to the dir listed. Running in a DOS window the command does work if I manually put in a filename. I'm sure there's something I'm not getting right here. COuld you point me in the right direction? Thanks!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Not familiar with 7zip, but this doesn't look right to me:

Arguements: e @[User::FileName] -o:c\dep\unzip

Not sure what you need, but I think the use of the SSIS variable is incorrect. Unfortunately, my SSIS books are at work, so I have nothing to check it with.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Okay, I was looking at one of my packages, and I think I know what you need to do.

Edit the Execute Process task. Select Expressions. Click on the ellipses button (...) and in the Property Expressions Editor, click under Property, click the drop down arrow, and select the property Arguments, then click the (...) under Expression and then build your string there.

pop SSCrazy Points: 2130 More actions · Answer 5

thanks Lynn.

The expression I needed under Arguments was : " e " + @[User::FileName] + " -oc:\\dep\\unzip -aoa"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Glad to help, and from what you said above, I assume it worked okay.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 7

I have been stuck on this unzip thing for a while now. Because you have had some success with 7Zip (Never heard of it until now) I downloaded it. I have gotten closer, the program executes but it says it can not find the archive.

Now I have a PRINT statement to show me the statement I am sending to Windows. If I copy paste this the extraction works.

Any ideas on this??? I really need help. Here is my code:

declare @ZipFileName VarChar(max)

declare @SQLCommand VarChar(max)

SET @ZipFileName = 'C:\Users\jwilliams\Documents\EConnect\FirstMerrit\SourceFiles\810571sn.zip'

SET @SQLCommand =

'exec master..xp_cmdshell ' + '''' + 'C:\7zip\7zip\7z.exe e -oc '

+ @ZipFileName + ' -pP810571 ' + ''''

PRINT 'Extraction Command sent: ' + cast(@SQLCommand as varchar(max))

PRINT ''

EXEC (@SQLCommand)

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

If you can use third-party solutions, check the commercial CozyRoc Zip Task. It supports Zip, GZip, Tar, Unix, BZip2 and it is completely integrated in SSIS.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 9

Thanks. I got 7Zip working. The solution works perfectly for unzipping and I will use it to ZIP an XLS document that I generate after working with the Unzipped data.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh