Friday 12 October 2012

Custom SSIS Component: Zip Task

In January 2011 I did a post about archiving processed source files in a zipfile via a Script Task. Because not everybody is fond about programming, I decided to make my own Zip Task (and UnZip task). For the actual zipping I used the well know opensource DotNetZip library.

This Zip Task is still beta and more zip features will follow. Please use it, test it and let me know your findings or wishes in the comments below.
The Zip Task is now stable, but please keep submitting suggestions for new features.
Zip Task 2008 Beta




















Zip Task 2012 V1.2























Here is an example on how to add this Zip Task in your BIML Script.

Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 and 2012 version on the download page.

BETA2: Various validations added, New option added to append to an existing zip file, Switch from SharpZipLib to DotNetZip because it had better append possibilities.
BETA3: Bug solved when using a variable as destination.
V1.0: Bug solved that didn't validate driveletters with smallcaps
V1.1: Option added to delete source files after zipping (see properties). Bug solved that permitted Asian chars in filenames. Bug solved that added extra folder in zipfile. Code added for easier upgrading to new release.
V1.2: Finetuned code to allow more than 65535 files and to allow very big files (zip64).
V1.3: 2016 version added and made task upgradable in SSDT 2016. Added 64 installer.

Installation
The installer registers the DLL in the GAC and copies it to the task folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup. Restarting SQL Server Data Tools is not necessary.

How add the task the the toolbox (2008 only)
When you open a SSIS project in Visual Studio/Bids, right click the toolbox and choose "Choose Items...". After this Visual Studio will be busy for a couple of seconds!

Right click toolbox






















When the Choose Toolbox Items window appears, go to the SSIS Control Flow Items and search for the newly installed Zip Task and select it. Click ok to finish.


Choose Toolbox Items




















Now the new task will appear in the toolbox. Ready to use! Have fun.
New task added



























Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom task.





51 comments:

  1. Minor bug in beta: if you cancel the add variable window, it will also cancel the editor window.

    ReplyDelete
    Replies
    1. Thanks for reporting. I have solved it and it will be in the next release.

      Delete
  2. Strange Error: TITLE: Package Validation Error
    ------------------------------

    Package Validation Error

    ------------------------------
    ADDITIONAL INFORMATION:

    Error at Zip Task: The Validate method on the task failed, and returned error code 0x80004003 (Object reference not set to an instance of an object.). The Validate method must succeed and indicate the result using an "out" parameter.

    Error at Zip Task: There were errors during task validation.

    (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    No matter what I've tried, this error persists.

    ReplyDelete
    Replies
    1. Hi anonymous,

      Could you tell me what you filled in the editor? En was the error on runtime or design time?

      Regards,

      Joost

      Delete
    2. I get that same error if I use a variable as source or destination. It works fine if I use connection manager for both.

      Delete
    3. Thank you for providing the details about the variable. I found the bug and solved it. New version is available for download.

      Delete
  3. Hi,

    is it possible to decompress PPMd format? I think no...it is not supported by DotNetZip library. In the case, do you have any suggestion?
    Thanks a lot

    ReplyDelete
    Replies
    1. No this task only supports zip... try to find a .Net library for PPMd and use a Script Task or find an application that supports PPMd and use the Execute Process Task.

      Or post a question in the SSIS Forum

      Delete
  4. D:\folder validates, but d:\folder not.

    ReplyDelete
    Replies
    1. Thank you for reporting this bug. It has been solved in V1.0

      Delete
    2. Thanks for the super quick fix!

      Delete
  5. When I zip a specific file, is there a way to exclude the path?

    Source (variable): C:\Product\Customer 001\OutputFile.txt
    Destination (variable): C:\Product\Customer 001\OutputFile.txt.zip

    Zip file contains: Product\Customer 001\OutputFile.txt

    Can we put the file in the root of the zip instead of in the same folder structure it lives in?

    --Cam

    ReplyDelete
    Replies
    1. Hi Cam,

      Will check it and try to come up with a solution.

      Regards,

      Joost

      Delete
    2. Hi Cam,

      This bug will be solved in the next version. Probably next week, still doing some tests.

      Regards,

      Joost

      Delete
  6. When using this it works great, but it just completely ignores filenames that contain Unicode Characters like "主图2.jpg" is there anything I can do to enable this or is this a bug in the package? I love the simple usage of the package.

    ReplyDelete
    Replies
    1. Will check it and try to come up with a solution.

      Regards,

      Joost

      Delete
    2. Thanks! I found a work-around in the meantime by removing the Unicode Characters, but having Unicode Characters supported would be wonderful.

      Delete
    3. Hi Randy,

      Found a solution... has to do with encoding used in the zip file. It worked when I converted the names to UTF8. Will do some more testing and post an update in a couple of days.

      Regards,

      Joost

      Delete
    4. Thanks - I just discovered some relevant information though. Microsoft treats "Unicode" characters as UTF-16. This means that even converting to UTF8 may not be quite enough, but I will gladly test the updated version.

      Delete
    5. Hi again, Can you explain more about how you converted the file names to UTF8 prior to sending them to the Zip Task or if you did issue an updated version? Thanks!

      Delete
    6. It was a standard feature within the DotNetZip library. I added it in version 1.1

      Delete
    7. Just tested with V1.2 (I didn't see V1.1 had come out) and everything works! THANKS!

      Delete
  7. I'm zipping a 9.77GB text file which I would expect to produce an output zip file of ~1.5GB and the Zip task is erroring with:
    [Zip Task] Error: Zip error: Compressed or Uncompressed size, or offset exceeds the maximum value. Consider setting the UseZip64WhenSaving property on the ZipFile instance.
    I can't see this property exposed anywhere. Can you help? Apart from this, the task is very useful.

    ReplyDelete
    Replies
    1. Hi Warkboy,

      Thanks for using my tasks and for the feedback. It's a property of the zip library I use. Will check it out and try to put it in the next version as a changeable property of the task.

      Regards,

      Joost

      Delete
    2. Hi Warkboy,

      There is a new version available that allows larger zip files.

      Regards,

      Joost

      Delete
    3. I'm afraid this still exhibits the same error. Trying it with a 9.90 GB file

      Delete
    4. Perhaps there is some limit per file. I just zipped a whole bunch of files into a 11GB zipfile without any problems. Will do some more testing in the near future...
      Which SSIS version are you using and are you running in 64bit?

      Delete
  8. Thank you for the helpful component!

    ReplyDelete
  9. Nice work on the component, thanks! As a feature, could you add the option to split the final zip file into multiple files? I currently use WINZIP to manually zip a 27 GB text file into approx. 9 zip files of 665 MB each. Makes for more reliable FTP transfers but is tedious :(

    ReplyDelete
    Replies
    1. Thanks! Will take that into consideration for possible future versions.

      Delete
  10. Does your zip task work with GZIP yet?

    ReplyDelete
    Replies
    1. No, but if I got enough request I will add GZIP support.

      Delete
    2. Gzip report would be VERY beneficial. And compression settin ()-10 for speed vs compression). Polybase in SQL 2016 and Azure SQL DW only supports gzip formatted files for extended tables

      Delete
  11. Does this task need to be installed on the deployment server as well? Or is it included when a project is deployed?

    ReplyDelete
    Replies
    1. Yes you need to install thirdparty components on the deployment server as well!

      Delete
  12. Hi Joost! Thanks for this great component.

    I just have a question - I'm able to run my package using SQLAgent if I run in 32-bit runtime. But when in 64-bit the job fails with this error (Cannot create a task from XML for task "ZIP create rar", type "Zip Task" due to error 0x80070057 "The parameter is incorrect").

    Do you know why this works in 32bit but not 64bit?

    Thanks
    Geoff

    ReplyDelete
    Replies
    1. Hi Skech, It should work in both 32 and 64 bit. Which version are you using? And which version of SSIS are you using? Then I can do some testing.

      Delete
    2. Thanks for the quick reply! I'm using 'ilionx.SSIS.Tasks.ZipTaskInstaller2014V1.3'. SQL Server 2014 and Visual Studio 2013. There are no issues when I debug the SSIS solution. It only fails when trying to run the package from SQLAgent.

      Please let me know if you need any more information! I hope we can figure this one out :)

      Delete
    3. Is the sql agent running the packages on the same machine? Or on a different machine that doesn't have the zip task installed?

      Delete
  13. Everything is on the same machine. The job only runs successfully if I set the package to run in 32-bit runtime.

    ReplyDelete
    Replies
    1. Thanks. I will investigate it this weekend and try to find a solution (if necessary). Thanks for the feedback.

      Delete
    2. I found the problem. Copy the xml files ZipTaskExtensions.xml and ZipTaskMapping.xml from C:\Program Files (x86)\Microsoft SQL Server\120\DTS\UpgradeMappings to C:\Program Files\Microsoft SQL Server\120\DTS\UpgradeMappings

      After that it should work. I need to adjust the installer for this, but at the moment it wont copy to both the 64bit and 32bit folder.

      Delete
  14. Awesome, thank you so much for assisting me with this Joost! I really appreciate it.

    ReplyDelete
    Replies
    1. Thank you for reporting the bug! I have added a separate 64bit installer as well. You can install both of them side by side.

      Delete
  15. Installed this too on SQL Server 2012.

    Its causing too much freezes, had to uninstall it too. Otherwise was thinking to put the DLLs on deployment server.

    Can you suggest any workaround ?

    ReplyDelete
    Replies
    1. Never had complaints about performance before. Are you using large files (can you zip them manually)? Does it finish OK eventually? Which version are you using?

      Delete
  16. I have installed both the 64bit and 32bit versions, but I do not see them in Visual Studio 2015 (v14.0.25431.01 Update 3)

    ReplyDelete
    Replies
    1. Make sure you installed on the same drive as SQL Server.

      Delete
  17. This is a great ssis addon. Any chance a "remove-files" feature could be added which could delete the input file(s)?

    ReplyDelete
    Replies
    1. It's already there, but only visible in the properties of the task (not in the editor).

      Delete
    2. I see it now, 'DeleteSource'. Thank you for the quick reply.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...