Solved

HELP - Unzip .Z Files using 7zip and SSIS Execute Process Task

Posted on 2010-09-08
16
5,360 Views
Last Modified: 2013-11-10
Hi,

I am trying to extract \ decompress a .z file using 7 zip and Execute Process Task.

Error message recieved
[Execute Process Task] Error: In Executing "C:\Program Files\7-Zip\7z.exe" "e\\Londb6\Test Data\BT D9686601\Aug 10\D9686601.D578300261.DWC1449.Z-o\\Londb6\Test Data\BT D9686601\Aug 10\To be Loaded " at "", The process exit code was "7" while the expected was "1".

I have followed the follwing thread to try complete this task.
http://sqlserversolutions.blogspot.com/2008/10/zip-and-unzip-files-in-folder.html

I cannot get this to work for me and am sure this is a simple quick fix for someone who knows this task.

I have set up a foreach loop, the folder path to collect files from is listed as
\\Londb6\Test Data\BT D9686601\Aug 10
Files = *.Z*
Created a variable USER::FIlename

Within the foreachloop I created an Execute Process Task.
Executable = C:\Program Files\7-Zip\7z.exe
Then created an Expression
"e" + @[User::FileName]+ "-o\\\\Londb6\\Test Data\\BT D9686601\\Aug 10\\To be Loaded "

I also tried this by removing the \\londb6 and replacing the a drive letter being D:\ just to see if I can get this to work and I cant.

I'm assuming it's just the expression that's not right , any suggestions on what this should be would help !!

Thanks




0
Comment
Question by:EHardie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 33625698
error code 7 means there is a syntax error in command line. I think you should put space between "e" and "\\Londb6".
0
 

Author Comment

by:EHardie
ID: 33625732
[Execute Process Task] Error: In Executing "C:\Program Files\7-Zip\7z.exe" "e \\Londb6\Test Data\BT D9686601\Aug 10\D9686601.D578300261.DWC1449.Z-o\\Londb6\Test Data\BT D9686601\Aug 10\To be Loaded " at "", The process exit code was "2" while the expected was "1".

Put the space got the above error
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 33625762
Also space before -o. Also, filenames with spaces must be quoted:

e "\\Londb6\Test Data\BT D9686601\Aug 10\D9686601.D578300261.DWC1449.Z" -o"\\Londb6\Test Data\BT D9686601\Aug 10\To be Loaded"
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:EHardie
ID: 33625795
The result is from my variable....and expression within the task.

"e " + @[User::FileName]+ " -o\\\\Londb6\\Test Data\\BT D9686601\\Aug 10\\To be Loaded "

So I added a space after e and before -o as above.

So what additional changes should I make to the expression to get the result your suggesting ?

Here's the error
[Execute Process Task] Error: In Executing "C:\Program Files\7-Zip\7z.exe" "e \\Londb6\Test Data\BT D9686601\Aug 10\D9686601.D578300261.DWC1449.Z -o\\Londb6\Test Data\BT D9686601\Aug 10\To be Loaded " at "", The process exit code was "2" while the expected was "1".
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 33625820
You should double quotes arround file names with spaces. Not sure what syntax would be in SSIS. Something like this:

"e \"" + @[User::FileName]+ "\" -o\"\\\\Londb6\\Test Data\\BT D9686601\\Aug 10\\To be Loaded\""
0
 

Author Comment

by:EHardie
ID: 33625856
Sorry no that doesn't work
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 33625999
Sorry that didn't work. Anyway, to debug your problem, try to execute generated command manually. Maybe you will see where is the error in command line, or maybe you will get some meaningful error message.
0
 

Author Comment

by:EHardie
ID: 33626195
I have never used this function before or used 7 zip, how would I execute it manually ?
I
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 33626255
I guess you already know command you are executing:
"C:\Program Files\7-Zip\7z.exe" "e "\\Londb6\Test Data\BT D9686601\Aug 10\D9686601.D578300261.DWC1449.Z" -o"\\Londb6\Test Data\BT D9686601\Aug 10\To be Loaded" "

If you can, open cmd (Start->Run->cmd) on your SQL server machine and execute this command. If you can't login to SQL server machine, you can try same on your computer
0
 

Author Comment

by:EHardie
ID: 33626499

Thanks I'll give it a try.

What I have been able to do so far is the following;

I removed the foreachloop and the expresstion.
In the execute process task within the Argument specified ;
e "\\Londb6\Test Data\BT D9686601\Aug 10\"
then set the working folder  as;
D:\Test Data\BT D9686601\Aug 10\To be Loaded

It unzips this files and places them into the working folder but does also fail the task.

It also picks up ALL zipped files in subfolders in this location and copies into the working directory which is a pain as I only want the files directly in Aug 10 unzipped. It seems to search all sub folders in this location and if it finds any unzips them too.

any ideas ?

0
 
LVL 19

Expert Comment

by:Rimvis
ID: 33633786
>> but does also fail the task
What error do you get?

>>It also picks up ALL zipped files in subfolders
That's strange. As I understand, 7zip should scan subdirectories only if -r switch is used.
0
 

Author Comment

by:EHardie
ID: 33636690
Hi I was getting the same error as previously The process exit code was "2" while the expected was "1".

This was through SSIS.

It does however unzip the files in the required location but gives a failure message.

I can't understand why it goes through sub folders either ! :(
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33638553
Try the below Steps ..see the screen shots below

1. Create a string Variable FileName and place the complete path of one of your zip files in the Folder
2. For each loop --> Folder (browse to your folder) ---> Files (*.Zip) --> retrieve FileName (SELECT Full Qualified) NOTE: DO NOT check traverse Sub folders
3. In variable Mappings --> Select the variable - FileName
4. Place an Execute Process task  inside FOR Each Loop
5. Executable --> point it to 7zip.exe file
6. Working Directory ---> D:\Test Data\BT D9686601\Aug 10\To be Loaded
7. create an expression for Argument as "e " +  @[User::FileName]

your fileName variable should have something like
D:\Londb6\Test Data\BT D9686601\Aug 10\FileName.Zip

ExecuteProcessTask.jpg
ExecuteProcessTask1.jpg
FEL.jpg
Variable.jpg
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34109445
Hi,

use command
c:7zip.exe  e archive.zip expandlocation

0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 34505795
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question