Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2010-09-08
16
Medium Priority
?
6,016 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
15 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 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