Sunday, 14 October 2012

Custom SSIS Component: UnZip Task

In january 2011 I did a post about unzipping files within SSIS with a Script Task. Because not everybody is fond about programming, I decided to make my own UnZip (and Zip) Task. For the actual unzipping I used the well know opensource DotNetZip library.





This UnZip Task is still beta and more unzip features will follow. Please use it, test it and let me know your findings or wishes in the comments below.
The UnZip Task is now stable, but please submit suggestions for new features.






















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: UNC path support, cancel variable window bug solved.
BETA3: Various validations added, New option added to store unzipped filepath in variable.
BETA4: Switched to DotNetZip for the actual unzipping * Probably last beta version before official release. *
V1.0: Bug solved that didn't validate driveletters with smallcaps
V1.1: Option added to delete zip file after unzipping it (see properties). Code added for easier upgrading to new release.
V1.2: SSIS 2016 added and now upgradable. 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 UnZip 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.




Geplaatst door Joost van Rossum
Labels: , , ,

64 comments:

  1. Anonymous14 October 2012 at 21:49

    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:51

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

      Delete
  2. Unknown16 October 2012 at 21:43

    Hello, sweet program, however I selected a .zip from my directory, and pointed at the same directory to place the .csv file out of it. It errored out and said incorrect file type...but it is a .zip

    ReplyDelete
    Replies
    1. Joost van Rossum16 October 2012 at 21:57

      Hi Andrew,

      Thanks for reporting the bug. Did you use variables or file connection managers?

      Regards,

      Joost

      Delete
  3. Unknown16 October 2012 at 21:58

    Actually, the file is supported. However, when the file is on a server, it can't be referenced, when it is on my local hard drive it works just fine. Is there a problem with security here?

    ReplyDelete
    Replies
    1. Unknown16 October 2012 at 22:02

      Yes, exactly.

      Delete
    2. Joost van Rossum16 October 2012 at 22:06

      I have found the bug. Testing the format of the path isn't working with network shares starting with \\
      Will try to solve this tomorrow and upload a new version. Thanks again for reporting the bug.

      Delete
    3. Unknown16 October 2012 at 22:08

      Thanks for the help. I'll be looking on this blog post for the new version tomorrow! That'd be awesome.

      Delete
    4. Joost van Rossum16 October 2012 at 23:14

      I have made some changes in the path format checking so that UNC path will pass the test. In the next version I will also add that validation to the editor (now it's only doing it on runtime).

      Delete
  4. Unknown18 October 2012 at 19:10

    I'm assuming I just need to log in to my servers and install the DLL's...I will try this.

    ReplyDelete
    Replies
    1. Joost van Rossum18 October 2012 at 19:21

      Yes you need to install it on the server as well.

      I expect a new version this weekend. That will probably be the last beta version before I will release a final version.

      Delete
    2. Unknown18 October 2012 at 19:56

      One last question :) By uninstalling the program from Add/Remove does that also uninstall all related DLL's and other files...

      Delete
    3. Joost van Rossum18 October 2012 at 20:16

      Yes, uninstalling will remove the 2 dll's from the sql folder and the 3 dll (2 for task and 1 unzip lib) from the GAC. So eveything will be the same as before installing the task.

      Delete
  5. Anonymous19 October 2012 at 22:46

    When adding a new variable or connection manager, they aren't added to the other lists. They only appear after closing and re-opening the task.

    ReplyDelete
    Replies
    1. Joost van Rossum19 October 2012 at 23:00

      Hi Anonymous,

      Thanks for reporting. I solved this in Beta 3.

      Regards,

      Joost

      Delete
  6. Anonymous1 November 2012 at 19:20

    I'm receiving following error: [UnZip Task] Error: Unzip error: Wrong Local header signature: 0x8088B1F. Any ideas on what it may be?

    ReplyDelete
    Replies
    1. Joost van Rossum1 November 2012 at 22:18

      Hi Anonymous,

      I think the SharpZipLib library that I used doesn't handle all zip files. I had some problems with it while creating my Zip Task. Give me a couple of days to switch to the same library as my zip task. Watch this page or follow me on twitter @ssisjoost

      Delete
    2. Joost van Rossum5 November 2012 at 00:07

      Hi Anonymous,

      I uploaded a new version. Let me know if it works for you.

      Regards,

      Joost

      Delete
    3. Anonymous5 November 2012 at 19:42

      When I try to install Beta 4 version it gives me error: Couldn not access network location Taks\. Any ideas?

      Delete
    4. Joost van Rossum5 November 2012 at 20:07

      Did you run the installer as administrator?

      Delete
    5. Anonymous5 November 2012 at 23:32

      yes, I did

      Delete
    6. Joost van Rossum6 November 2012 at 08:44

      Did previous versions install without problems? It seems you don't have access to the task folder of sql server. What folder is showing in the installer? Should be something like:
      C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\

      Delete
    7. Unknown23 April 2015 at 20:43

      Getting the same error. Downloaded the 2014 version. Attempting to run on Win2008R2 with SQLServer2008R2 and BIDS2008. Running as Admin. I granted Modify perms to all folders in the below path:

      C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\

      However, the installer is showing the following path when displaying error:

      Tasks\

      Any ideas?

      Delete
    8. Joost van Rossum23 April 2015 at 21:56

      @Jeremy what about installing the 2008 version since you use SSIS 2008?

      Delete
    9. Unknown24 April 2015 at 20:49

      *facepalm

      Works. Thanks.

      Delete
    10. Joost van Rossum24 April 2015 at 22:11

      :-)

      Delete
  7. Anonymous13 November 2012 at 22:50

    Can I ask whether this unzips .gz files or just .zip files? Thanks!

    ReplyDelete
    Replies
    1. Joost van Rossum14 November 2012 at 08:58

      for now just traditional zip. If there is a high demand for other types then I will consider it for next versions.

      Delete
    2. Ales17 October 2013 at 11:27

      I woud also need to unzip *.gz files :) Thanks!

      Delete
  8. Anonymous16 November 2012 at 16:20

    Hello, I am getting this error when I try to run one of my jobs that uses Task UnZip as part of the SSIS package. Please advise:

    Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation.
    All rights reserved.
    Started: 7:14:39 AM
    Error: 2012-11-16 07:14:41.61
    Code: 0xC0010018
    Source: Unzip Task
    Description: Failed to load task "Unzip Task", type "".
    The contact information for this task is "Marc Potters and Joost van Rossum, Ilionx Noord Nederland.". End Error

    Error: 2012-11-16 07:14:41.70
    Code: 0xC0010026
    Source: Unzip Task
    Description: The task has failed to load.
    The contact information for this task is "Marc Potters and Joost van Rossum, Ilionx Noord Nederland.".
    End Error
    Error: 2012-11-16 07:14:41.70
    Code: 0xC0024107
    Source: Unzip Task
    Description: There were errors during task validation.
    End Error DTExec: The package execution returned DTSER_FAILURE (1).
    Started: 7:14:39 AM
    Finished: 7:14:41 AM Elapsed: 2.293 seconds.
    The package execution failed. The step failed.

    I do have confirmation that the UnZip Task was installed on the server.

    ReplyDelete
    Replies
    1. Joost van Rossum16 November 2012 at 19:37

      Hi Anonymous,

      Could you please contact me via the form. I need some additional info like version of the unzip task. And let me know if you're using variables or connection managers.

      Regards,

      Joost

      Delete
    2. Anonymous30 November 2012 at 15:55

      Hello Joost,

      Sorry for the late response, I was working blind (not able to develop on production, which is where I was getting the error.) But I was able to resolve it with no issues. It had to do with variables in the end.

      Thanks for the quick response and the great tool!

      Delete
  9. Anonymous16 December 2012 at 20:44

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

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

      Thank you for reporting this bug. It is now solved.

      Delete
  10. Anonymous8 February 2013 at 03:45

    I am trying to unzip 5 zip files in a folder 'D:\zipfiles' so I am trying to use this task within the Foreach loop task, and I am passing the file naue using variable, but it throws an error when I try to do it and doesn't accept the file name using this way.
    Please advise how can I do it.

    ReplyDelete
    Replies
    1. Joost van Rossum15 February 2013 at 14:54

      Make sure there is a valid value in the variable or use the DelayValidation option.

      Delete
  11. Anonymous14 May 2013 at 15:43

    I am guessing no, but would this work on SQL Server/BIDS 2005?

    ReplyDelete
    Replies
    1. Joost van Rossum14 May 2013 at 17:22

      No it's not working for 2005, but there are others: http://taskunzip.codeplex.com/

      Delete
  12. Anonymous28 July 2013 at 18:38

    Hello, could you please help on the following:
    I'm unzipping the zip archive to folder, but there are 3 files in it.
    I want to get all the three file names in a variable to process all of them further.

    But I get only one (last in the list) file name.
    Is there a way to get all file names?
    Thanks

    ReplyDelete
    Replies
    1. Joost van Rossum28 July 2013 at 20:39

      That option is only useful if you have only one file to unzip.

      If they all have the same format then you could use a Foreach File Enumerator to loop through the unzipped files and process them.

      If they don't have the same format you could unzip only one file at a time (use the filters) and then you can use that option to store the filepath in a variable.

      Delete
  13. Ales17 October 2013 at 11:25

    I woud also like to unzip *.gz files. Can I fix this myself or you must upload new setup? Thanks for replay. Aleš

    ReplyDelete
    Replies
    1. Joost van Rossum17 October 2013 at 11:40

      gz is not (yet) supported. Perhaps in future if more people request this feature...

      Delete
  14. Princesszea1219 November 2013 at 02:13

    Hi,

    I’ve just downloaded the unzip task which works fine on my local machine. As soon as I run it as a job on my production server I get this error message

    bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 01:37:22 Error: 2013-11-19 01:37:22.35 Code: 0xC0010018 Source: Unzip Task Description: Failed to load task "Unzip Task", type "ilionx.SSIS.Tasks.UnZip, ilionx.SSIS.Tasks.UnZip, Version=1.0.0.0, Culture=neutral, PublicKeyToken=e6e8a670e7afaeac". The contact information for this task is "Marc Potters and Joost van Rossum, Ilionx Noord Nederland.". End Error Error: 2013-11-19 01:37:22.37 Code: 0xC0010026 Source: Unzip Task Description: The task has failed to load. The contact information for this task is "Marc Potters and Joost van Rossum, Ilionx Noord Nederland.". End Error Error: 2013-11-19 01:37:22.37 Code: 0xC0024107 Source: Unzip Task Description: There were errors during task validation. End Error Error: 2013-11-19 01:37:22.37 Code: 0xC0010025 Source: UnZipFTPFile Description: The package cannot execute because it contains tasks that failed to load. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 01:37:22 Finished: 01:37:22 Elapsed: 0.219 seconds. The package execution failed. The step failed.

    ReplyDelete
    Replies
    1. Joost van Rossum19 November 2013 at 07:15

      Did you install the unzip add-onon your production server?

      Delete
  15. Princesszea1219 November 2013 at 22:41

    Thanks, for your quick reply - I will install the add- on on our production server.

    ReplyDelete
  16. Princesszea1220 November 2013 at 19:54

    Hi, Quick question, once I install the add-on on our production server - how will this affect the other packages that are on that server which use the foreach loop and does not have the FTP File option in the Foreach Enumerator, as I don’t want to break other peoples packages on the production server.

    ReplyDelete
    Replies
    1. Joost van Rossum20 November 2013 at 20:35

      As long as the other packages don't use older versions of the component you are about to install there is no problem! It won't affect packages that don't use the ftp enumerator.

      Delete
  17. Niall31 December 2013 at 03:45

    Hi Joost,
    Thank you very much for creating this tool - I have sent an email via your contact form, however thought I would also post here in case anyone else is having the same issue. I am trying to unzip files created and compressed by a mainframe, using (I think) a "shrink" command. These files unzip correctly using WinRAR, however I am rebuilding the distribution application and need to unzip using SSIS. When I use your tool, I get the following error. Do you have any plans to include different methods of compression/uncompression to your tool?

    SSIS package "RCMS.dtsx" starting.
    Error: 0x0 at Unzip Task, UnZip Task: Unzip error: Entry CBF.TXT uses an unsupported compression method (0x01, Shrink)
    Task failed: Unzip Task

    ReplyDelete
    Replies
    1. Joost van Rossum31 December 2013 at 14:42

      What is the exact format of the compressed file? Is it a zip file and can you unzip/open it in windows without tools like winrar or 7zip? The library I use (http://dotnetzip.codeplex.com/) only supports zip files.

      Delete
  18. James Bloor27 March 2014 at 12:51

    Hi

    Can you please help? I'm new to SSIS so I'm still finding my way, your Unzip component is really useful however I'm struggling to use a fore reach loop and variables to set the name of the ZIP file to be unzipped (I need to unzip a single daily file with a name that changes every day).

    Your help with this would be much appreciated.

    Thanks

    ReplyDelete
    Replies
    1. Joost van Rossum27 March 2014 at 13:22

      You can use the variable from the foreach loop as a source in the Unzip task. Make sure the variable is filled with a default value like d:\yourfile03272014.zip so that the task can validate the values.

      Delete
  19. Unknown26 April 2014 at 22:00

    I am expecting to receive the zip file with different names everytime for example. HSB_20142404.zip and next day i would get HSB_20142405.zip so basically I would need to pickup the zip file and unzip it regardless of wht the name is (Dynamically point to the zip file and extract it).. Can someone please help me step by step.

    ReplyDelete
    Replies
    1. Joost van Rossum26 April 2014 at 22:55

      Either use an expression to determine the filename or if it's the only file in the folder then you could use a Foreach Loop to get the filename/path.

      Delete
  20. Unknown17 August 2015 at 15:32

    Does you unzip task work with .gz archives or is that functionality you never added?

    ReplyDelete
    Replies
    1. Joost van Rossum17 August 2015 at 20:29

      If there is a high demand for it and there are no alternatives then I will consider adding it. At the moment I got 3 requests.

      Delete
    2. Unknown18 August 2015 at 17:35

      Okay, thanks.

      Delete
  21. Erik van Dongen16 November 2015 at 14:27

    Hi Joost,

    I have installed the SQL2012 version 1.1 on my DEV machine and everything works fine. I have had a DBA install the same version on a test server but the package fails with no clear error. Just to contact you ;o) The error is the same as the one from PrincessZea12 on nov 19 2013 a few posts up.

    The zip from your download page has a .exe as well as a .msi. I used the msi. Is the exe different? Should my dba restart anything on the server after installing?

    Cheers and thx,
    Erik

    ReplyDelete
  22. Joost van Rossum18 November 2015 at 13:33

    Hi,
    Make sure the DBA uses admin rights to install, because the 2 DLL's will go in the GAC and in a sub folder of program files. Also make sure that the given path is correct. It should point to an existing folder.

    I Always use the setup.exe, but I think it just calls the msi file (it's an installer project from visual studio which creates both)

    ReplyDelete
  23. Unknown18 December 2015 at 05:47

    Hi Joost,

    This is a great utility !!!

    I installed recent version Unzip Task on SQL Server 2014 and keep getting the following error. Could you please help me to resolve this issue. Any assistance will be greatly appreciated...

    "Unzip SiteLink DB Backup:Error: The task has failed to load. The contact information for this task is ""Marc Potters and Joost van Rossum, Ilionx Noord Nederland."".
    "

    ReplyDelete
    Replies
    1. Joost van Rossum18 March 2016 at 15:56

      Sorry for the late reply, but try the new version. If that still fails contact me via the contact form in the menu.

      Delete
    2. Unknown18 March 2016 at 18:19

      Great. Thanks for your response. I will check the newer version and get back to you if I still encounter issues.

      Delete
  24. Unknown4 February 2016 at 14:01

    Hi Joost,

    I am using your beautiful unzip task on a Virtual machine hosted on Azure. I have to install the unzip task again on another VM but I get the following error when running the setup file:
    Cannot access network location Tasks\.

    Do you have any suggestions?
    Thanks in advance!

    ReplyDelete
    Replies
    1. Joost van Rossum17 March 2016 at 23:24

      Try the new version (1.2). That installer doesn't use the registry to get the task folder of SSIS.

      Delete
  25. Unknown26 November 2019 at 13:57

    Hi. i am using visual studio 2017 with SQL 2017. i tried installing your 2016 (2017 not avail) and also go into the DTS/task folder to copy th 2 dll folder into the 140 SQL server folder but it doesnt work....as in i cannot see the unzip task.
    You have any quick fix for it to be working?, thanks

    ReplyDelete

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.

Subscribe to: Post Comments (Atom)
Related Posts Plugin for WordPress, Blogger...