Solved

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

Posted on 2010-09-08
16
5,125 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

786 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