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


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.


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
            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;