Load File, Zip it and Delete from Source Folder in SSIS Package- SQL Server Integration Services(SSIS) Tutorial

Scenario:

We get multiple files in our Input Folder, we want to load those files to SQL Server Table and then zip each of them individually and loading to table.

Solution:

In this video we will learn step by step how to load multiple files and archive each of them once loaded to the table. We will learn following items in this video

1. How to read multiple Files from Input Folder by using Foreach Loop Container
2. How to use Data Flow Task to load Text files to SQL Server Table
3. How to Create Package Parameters for input Folder and Archive Folder 
4. How to create FileName variable so we can use in Foreach Loop Container to save file name on each iteration
5. Use Package Parameters and Variable in Script Task to Create Zip File and then add source file to it
6. How to use Script Task to Delete Loaded file
7. How to change .NET Framework version in Script task from 4 to 4.5 so we can add assemblies such as
8. System.IO.Compression and System.IO.Compression.FileSystem 

Script used in the video to zip and delete each file individually

public void Main()
        {


            //set the value to local variables
            string filename = Dts.Variables["User::FileName"].Value.ToString();
            string inputfilepath = Dts.Variables["$Package::InputFolder"].Value.ToString()+filename;
            string archpath = Dts.Variables["$Package::ArchFolder"].Value.ToString() +
                   filename.Replace(".txt", ".zip");


            //Create Zip File and then Add file to it
            using (ZipArchive zip = ZipFile.Open(archpath, ZipArchiveMode.Create))
            {
                zip.CreateEntryFromFile(inputfilepath, filename);
            }

            //Delete the file that is compressed and load to sql table
            File.Delete(inputfilepath);
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }

Load,Zip and Delete Files in SSIS Package - SSIS Tutorial

    Related Posts/Videos on Zip / UnZip by Script Task  

• How to change .NET Framework version in Script Task and Add Reference to Assembly(ZipFile Demo)
• Load Text Files,Zip them to Folder with Datetime and Delete From Input Directory in SSIS Package
• Extract Files From Zip Files and Delete the Zip Files once Unzipped in SSIS Package
• Zip ( Compress) Files and Add them to Folder According to Extension in SSIS Package
• Zip or Compress Files according to the Name of Files in SSIS Package 
• Get File names from Zip Files and Insert into SQL Server Table in SSIS Package
• How to Zip ( Compress) Multiple Folders and Delete in SSIS Package
• Create Zip file per day with Date Only and Add Files to it in SSIS Package