▼

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. Anonymous14 October 2012 at 21:27

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

    ReplyDelete
    Replies
    1. Joost van Rossum14 October 2012 at 21:50

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

      Delete
  2. Anonymous7 November 2012 at 16:23

    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. Joost van Rossum7 November 2012 at 17:32

      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. Phil Schmidt12 November 2012 at 17:41

      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. Joost van Rossum13 November 2012 at 15:29

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

      Delete
  3. Anonymous8 November 2012 at 15:32

    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. Joost van Rossum8 November 2012 at 23:00

      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. Anonymous16 December 2012 at 20:45

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

    ReplyDelete
    Replies
    1. Joost van Rossum16 December 2012 at 21:50

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

      Delete
    2. Anonymous4 April 2013 at 23:58

      Thanks for the super quick fix!

      Delete
  5. Anonymous4 April 2013 at 23:58

    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. Joost van Rossum6 April 2013 at 23:10

      Hi Cam,

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

      Regards,

      Joost

      Delete
    2. Joost van Rossum31 May 2013 at 16:11

      Hi Cam,

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

      Regards,

      Joost

      Delete
  6. Unknown10 April 2013 at 22:16

    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. Joost van Rossum11 April 2013 at 14:07

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

      Regards,

      Joost

      Delete
    2. Unknown23 April 2013 at 17:34

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

      Delete
    3. Joost van Rossum31 May 2013 at 13:04

      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. Unknown26 August 2013 at 21:51

      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. Unknown5 November 2013 at 16:22

      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. Joost van Rossum5 November 2013 at 18:22

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

      Delete
    7. Unknown7 November 2013 at 23:22

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

      Delete
  7. Anonymous19 July 2013 at 14:54

    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. Joost van Rossum19 July 2013 at 15:53

      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. Joost van Rossum23 September 2013 at 07:52

      Hi Warkboy,

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

      Regards,

      Joost

      Delete
    3. Anonymous10 March 2014 at 18:30

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

      Delete
    4. Joost van Rossum30 March 2014 at 23:01

      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. bc18 December 2013 at 21:54

    Thank you for the helpful component!

    ReplyDelete
  9. dtcfl18 February 2015 at 06:37

    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. Joost van Rossum22 February 2015 at 08:40

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

      Delete
  10. Unknown18 August 2015 at 16:01

    Does your zip task work with GZIP yet?

    ReplyDelete
    Replies
    1. Joost van Rossum22 August 2015 at 12:42

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

      Delete
    2. Unknown6 December 2016 at 21:54

      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. Houndin1 September 2015 at 19:30

    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. Joost van Rossum1 September 2015 at 20:09

      Yes you need to install thirdparty components on the deployment server as well!

      Delete
  12. skech12 April 2016 at 17:50

    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. Joost van Rossum12 April 2016 at 21:52

      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. skech12 April 2016 at 22:13

      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. Joost van Rossum12 April 2016 at 22:33

      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. skech12 April 2016 at 22:39

    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. Joost van Rossum13 April 2016 at 21:02

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

      Delete
    2. Joost van Rossum14 April 2016 at 20:38

      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. skech14 April 2016 at 21:03

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

    ReplyDelete
    Replies
    1. Joost van Rossum15 April 2016 at 17:49

      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. Unknown15 July 2016 at 15:01

    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. Joost van Rossum1 August 2016 at 13:50

      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. Unknown6 December 2016 at 22:20

    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. Joost van Rossum7 December 2016 at 07:02

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

      Delete
  17. R6 February 2017 at 09:44

    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. Joost van Rossum6 February 2017 at 14:00

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

      Delete
    2. R6 February 2017 at 17:25

      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.