Solved

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

Posted on 2010-09-08
16
4,973 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
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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now