Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-08
16
Medium Priority
?
5,810 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

610 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