(SQL Server) Unzip all Files to a Single Directory (Ignoring Subdirectories in Zip)

See more Zip Examples

Demonstrates how to unzip all files into a single directory, ignoring the internal directory structure of the files in the .zip.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @outDir nvarchar(4000)
    SELECT @outDir = 'c:/temp/testUnzipDir'

    -- Make sure our output directory exists.
    DECLARE @fac int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @fac, 'DirEnsureExists', @success OUT, @outDir
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END

    DECLARE @zip int
    -- Use "Chilkat_9_5_0.Zip" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT

    -- In this example, the xml_files.zip contains some files in the subdirectories "dir1" and also "dir2/dir3".

    -- a1.xml
    -- b1.xml
    -- c1.xml
    -- dir1/a2.xml
    -- dir1/c2.xml
    -- dir2/dir3/c3.xml

    EXEC sp_OAMethod @zip, 'OpenZip', @success OUT, 'qa_data/zips/xml_files.zip'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Unzip all files into the directory c:/temp/testUnzipDir
    DECLARE @numUnzipped int
    EXEC sp_OAMethod @zip, 'UnzipInto', @numUnzipped OUT, @outDir
    IF @numUnzipped < 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT 'Num unzipped = ' + @numUnzipped

    -- After unzipping via UnzipInto, we have this:

    -- c:\temp\testUnzipDir\a1.xml
    -- c:\temp\testUnzipDir\b1.xml
    -- c:\temp\testUnzipDir\c1.xml
    -- c:\temp\testUnzipDir\a2.xml
    -- c:\temp\testUnzipDir\c2.xml
    -- c:\temp\testUnzipDir\c3.xml

    EXEC sp_OAMethod @zip, 'CloseZip', NULL


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @zip


END
GO