Anyone here use SSIS?

AsSiMiLaTeD

I have what should be a simple need and am having the hardest time figuring it out. I have a folder that contains multiple zip files and I need SSIS to loop through and unzip all of those files.

I use Foreach loops all the time to do processing so I know that part of the problem, but I'm having the hardest time getting the unzip process to work.

On3s&Z3r0s

Does SSIS have a built-in zip library? Or are you calling out to something to do the unzip? Is there any reason it needs to be done in SSIS as opposed to PowerShell for instance?

AsSiMiLaTeD

SSIS does NOT have a built in zip library as far as I know. I've found a custom script written in C# that requires a separate library, but can't get it to work.

It's part of a very large job that sits in a running state, and really needs to be done in SSIS.

AsSiMiLaTeD

I found a way to execute a command line and use something like pkzip, but can't figure out how to use a variable as a parameter there, if I could just get that I'd be all set.

On3s&Z3r0s

That is a tough one. I was guessing there wasn't a native facility in SSIS for that since MSFT has never built convenient de/compression into any product. I've used SharpZipLib before, but it would require writing some custom code, probably as a little shell console app in C# or VB.NET. Then you could call that from SSIS. Unfortunately it would take a little time and wouldn't be something you could bang out in 15 minutes.

Found this though, which might help if you can put the looping together with it:
http://www.rafael-salas.com/2008/10/ssis-compress-files-using-execute.html

As far as using variables as a command line argument, can you use @variableName in place of the argument and let SSIS perform the substitution?

On3s&Z3r0s

Man, debugging SSIS sucks hard! I come to Club Polk to get away from this stuff, now this place is ruined for me. :mad:

Seriously, I think I figured out how to do the variable binding. When you open the Foreach Loop Editor's Collection settings you'll set up the Enumerator configuration on the target folder and select the correct radio button to get the form of the file name you want. I'd imagine you made it that far already. Then in the Variable Mappings settings you need to create a new variable. This variable will contain the file name returned by the enumerator (that you'll be unzipping). That should be it for the Foreach container.

Then you'll have the Execute Process Task within the Foreach. In the editor dialog for that, you go to Expressions and add a new expression for the Arguments property that uses the variable you mapped in Foreach (like @[User::fileName] for example. You can combine this with other hardcoded arguments, just remember to leave a space between them and escape backslashes or quotes if you need those in the literals. I made a little proof of concept with xcopy.exe and it worked, so I'd imagine you could do the same thing to iteratively call pkzip. In the thumbnails you can see that xcopy will get called with c:\source as the working directory, then the first argument passed to it will be the filename from the foreach and the second argument will be the name of the target directory. It's not exactly the same as your situation obviously, but illustrates variable mapping for parameters/arguments.

Good luck!

AsSiMiLaTeD

I got it figured out using 7-zip. basically what you described is what I was trying to do, but its picky about how you call a variable, and every zip program is different.

I did get it working though, and yes I agree debugging SSIS sucks...

On3s&Z3r0s

Cool, congrats!

nguyendot

ugh sql.

Sherardp

I would recommend 7-zip for this. Should be able to do what your looking for.

johnbobi21

I can help you short sale your property and never pay the bank another penny. Contact us for a free consultation.
When we talk, I will explain how the process works in detail and answer any questions you may have. Or, if you prefer, you can call me at (303) 495-3350
Thanks for reading this, Taylor Grant.
Taylor Grant is a Real Estate Agent at Colorado Custom Realty, LLC. Fort Collins Short Sales Realtor:
www.ColoradoHomeRescue.org
Phone: (303) 495-3350.

rooftop59

reported #11