none
How to zip file to excel file RRS feed

  • Question

  • Hi All,

      I have problem , while converting zip into excel file using ssis  (file system task).plz help me on this.


    nathan
    Tuesday, August 16, 2011 1:48 PM

Answers

  • .Net 3.5 comes with a System.IO.Packaging library you can use in a Script Task (so if you are on SSIS 2008 you have it). So there is no need to install anything or use any new tools/utilities.

    The full article with the code sample is at http://msdn.microsoft.com/en-us/library/system.io.packaging.zippackage%28v=VS.90%29.aspx#Y228


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Eileen Zhao Friday, August 26, 2011 10:02 AM
    Friday, August 19, 2011 2:09 PM
    Moderator
  • To unzip :

     http://dwhanalytics.wordpress.com/2010/06/14/unzip-using-ssis/

     

    To Convert this XML to XLS

    • Add one  Data Flow Task , drag one XML Source
    • Click on XML Source
    • Browse XML Source Location where you saved your xml file above
    • Click on Generate XSD and save this file to a location
    • Drag one Excel Destination Component and create connection to destination Excel



    Thanks http://dwhanalytics.wordpress.com/
    • Marked as answer by Eileen Zhao Friday, August 26, 2011 10:02 AM
    Saturday, August 20, 2011 12:04 PM

All replies

  • Hi Nathan,

    I guess you need to unzip a file and then if say a file is CSV make it Excel, or I am wrong?


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, August 16, 2011 2:09 PM
    Moderator
  • Here is an unzip example:
    http://microsoft-ssis.blogspot.com/2011/01/unzip-files-with-ssis.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Tuesday, August 16, 2011 2:15 PM
    Moderator
  • Hi Nathan,

    Do as below :

    1. UNZIP THE FILE USING SCRIPT.

    2 Then dumped into the excel.

     

    Its not possible to dumped the zip file into excel directly, what you need is to unzip first and then dumping

     

    Amit

    Please mark as answer if helpful

    http://fascinatingsql.wordpress.com/

    Tuesday, August 16, 2011 2:49 PM
  • Thanks For the reply ,but

     

    i tell process in ssis Which is  currently handling

     

                                  source file is   Zip format and  the file in  ftp server

     

                                 so using FTP transfer task get the file and loaded into local drives

     

                                      after that zip file converted into unzip

     

                                      after unzipping file in xml format

     

                                 then this xml file i convert into excel.

     

    this is the way i want to do using  ssis. so am struggling while converting zip file to unzip and xml to xlsx using  Script task .

    if have any idea plz share it and help me on this ...

     

    thanks.


    nathan
    Friday, August 19, 2011 8:20 AM
  • To extract a zip file, I always use the free tool 7z using the command line (Execute process task). Depending on the complexity of the xml file us can use the XML task to transform the file or directly the XML source and transfer data to excel.
    Friday, August 19, 2011 1:33 PM
  • .Net 3.5 comes with a System.IO.Packaging library you can use in a Script Task (so if you are on SSIS 2008 you have it). So there is no need to install anything or use any new tools/utilities.

    The full article with the code sample is at http://msdn.microsoft.com/en-us/library/system.io.packaging.zippackage%28v=VS.90%29.aspx#Y228


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Eileen Zhao Friday, August 26, 2011 10:02 AM
    Friday, August 19, 2011 2:09 PM
    Moderator
  • To unzip :

     http://dwhanalytics.wordpress.com/2010/06/14/unzip-using-ssis/

     

    To Convert this XML to XLS

    • Add one  Data Flow Task , drag one XML Source
    • Click on XML Source
    • Browse XML Source Location where you saved your xml file above
    • Click on Generate XSD and save this file to a location
    • Drag one Excel Destination Component and create connection to destination Excel



    Thanks http://dwhanalytics.wordpress.com/
    • Marked as answer by Eileen Zhao Friday, August 26, 2011 10:02 AM
    Saturday, August 20, 2011 12:04 PM
  • .Net 3.5 comes with a System.IO.Packaging library you can use in a Script Task (so if you are on SSIS 2008 you have it). So there is no need to install anything or use any new tools/utilities.

    The full article with the code sample is at http://msdn.microsoft.com/en-us/library/system.io.packaging.zippackage%28v=VS.90%29.aspx#Y228


    Arthur My Blog
    By: TwitterButtons.com


    LITTLE WARNING: This class can only unzip files that are zipped with this same class. It adds a file "[Content_Types].xml" when zipping and that same file is required when unzipping.

    See comments at: http://msdn.microsoft.com/en-us/library/system.io.packaging(VS.85).aspx

     

    While Packages are stored as Zip files*, all Zip files are not Packages. Packages have special requirements such as URI-compliant file (part) names and a "[Content_Types].xml" file that defines the MIME types for all the files contained in the Package. The Package class cannot be used to open arbitary Zip files that do not conform to the Open Packaging Conventions standard. For further details see Section 9.2 "Mapping to a ZIP Archive" of the ECMA International "Open Packaging Conventions (OPC)" standard, http://www.ecma-international.org/publications/files/ECMA-ST/Office%20Open%20XML%20Part%202%20(DOCX).zip (342Kb) or http://www.ecma-international.org/publications/files/ECMA-ST/Office%20Open%20XML%20Part%202%20(PDF).zip (1.3Mb) *You can simply add ".zip" to the extension of any Package-based file (.docx, .xlsx, .pptx, etc.) to open it in your favorite Zip utility.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Friday, August 26, 2011 11:42 AM
    Moderator