Unzipping and Archiving Files in SSIS

This article explains how to unzip a zip file containing text files, ingest the files into database tables, and then archive the files in a designated folder using SSIS. The solution assumes that the zip file contains a subfolder with the same name as the zip file, and all the files are located within that subfolder.

Solution

The following steps outline the solution for unzipping and archiving files in SSIS:

Step 1: Set up Project Parameters

Create the following project parameters:

        ·         ZipFolder: The folder where the zip file is located.

        ·         ZipFile: The name of the zip file without the ".zip" extension.

        ·         UnzipFolder: The parent folder that will hold the subfolder for the unzipped files.

        ·         ArchiveFolder: The parent folder that will hold the subfolder for the archived files.

        ·         ExecutablePath: The full path for 7z.exe (e.g., 'C:\Program Files\7-Zip\7z.exe').

Step 2: Define Variables

In the SSIS package, create the following variables with expressions:

        ·         unzip_folder_full: @[Project::UnzipFolder] + @[Project::ZipFile] + "\\"

        ·         archive_folder_full: @[Project::ArchiveFolder] + @[Project::ZipFile] + "\\"

        ·         current_file: Leave it blank.

Step 3: Unzip Files

In the SSIS package, add an Execute Process task with the following expressions:

        ·         Executable: @[Project::ExecutablePath]

        ·         Arguments: "x \"" + @[$Project::ZipFolder] + @[$Project::ZipFile] + ".zip\" \"-o" + @[$Project::UnzipFolder] + "\" -aoa"

Step 4: Create Archive Subfolder

Create a File System task that links to the Execute Process task above and specify the following values:

        ·         UseDirectoryIfExists: True

        ·         Operation: Create directory

        ·         IsSourcePathVariable: True

        ·         SourceVariable: User::archive_folder_full

Step 5: Iterate through Unzipped Files

Create a Foreach Loop task that links to the File System task created in the previous step.

        ·         In the Collection section:

o   Enumerator: Choose Foreach File Enumerator.

o   Add the following expression:

§  Directory: @[User::unzip_folder_full]

        ·         In Variable Mappings, map the variable User::current_file to Index 0.

        ·         In Properties, set DelayValidation to True.

Step 6: Process File and Move to Archive

Within the Foreach Loop task, perform the required tasks to ingest the file into a database table (not covered in this article), followed by a File System Task with the following specifications:

        ·         IsDestinationPathVariable: True

        ·         DestinationVariable: User::archive_folder_full

        ·         OverwriteDestination: True

        ·         Operation: Move file

        ·         IsSourcePathVariable: True

        ·         SourceVariable: User::current_file

Step 7: Delete Unzip Folder

Add another File System Task that links to the Foreach Loop task and configure the following specifications:

        ·         Operation: Delete directory

        ·         IsSourcePathVariable: True

        ·         SourceVariable: User::unzip_folder_full

Conclusion

By following the steps outlined in this article, you can effectively unzip a zip file, ingest the files into database tables, and archive them in a designated folder using SSIS. The provided solution leverages 7-Zip for the unzipping process and incorporates various SSIS tasks to facilitate the workflow.