Zip ( Compress) Files and Add them to Folder According to Extension - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We have different types of files in one of our folder. There are Excel files, CSV files, Text Files,BMP files. We want to Zip them according to the Extension. All the excel files should be Zipped to XLSX_Date. Once the file is zip, it should be deleted from the source folder.

Package should be able to handle any type of file automatically and zip them with Date if added to the source folder.


Solution:

To solve this requirement we will be using below Tasks in SSIS Package step by step
  1. Create Package Parameters for SourceFolder and ArchFolder so the path can be changed by using SSIS configuration according to the environment
  2. Create FileName variable so we can save file name with extension when loop through files by using  Foreach Loop Container 
  3. Create ZipFile Variable and write expression to build Full Zip File path, Here is the expression that we used in the video  @[$Package::ArchFolder] +Replace(Substring( @[User::FileName] ,FindString( @[User::FileName],".",1) ,5),".","")+"_"+Replace(Substring((DT_WSTR,30)GEtdate(),1,10),"-","_")+".zip"
  4. Change .Net Framework to 4.5 version in script task so we can add assemblies such as System.IO.Compression and System.IO.Compression.FileSystem
  5. Write Script to Create new Zip file if not exists according to extension and date. Then add the file to it and delete the file from source folder.
  6. If zip file already exists then add the file to it and delete the source file from Source Folder.


How to Zip files according to Extension of File Code used in the Script Task

public void Main()
        {

            //Assign values to local variable from SSIS Package Parameters and Variables
            string zipfile = Dts.Variables["User::ZipFile"].Value.ToString();
            string filename= Dts.Variables["User::FileName"].Value.ToString();
            string inputfilepath = Dts.Variables["$Package::InputFolder"].Value.ToString() + filename;


            //If zip File already exist for the same day, just add the files to it
            if (
            File.Exists(zipfile))
            {
                //MessageBox.Show(" Zip File does Exists");
                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                    
                    //Delete the file after zipping it
                    File.Delete(inputfilepath);
                }
               
                //If zip file does not exist for the day, create it and add files to it
            }
            else
            {
               // MessageBox.Show("File Does not exists");
                var fileStream = new FileStream(zipfile, FileMode.Create);
                fileStream.Close();

                using (ZipArchive addfile = ZipFile.Open(zipfile, ZipArchiveMode.Update))
                {
                    addfile.CreateEntryFromFile(inputfilepath, filename);
                }

                //Delete the file after zipping it
                File.Delete(inputfilepath);
               
            }

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



SSIS Tutorial - Compress different types of Files to Different Zip files as per Extension of File in SSIS Package



    Related Posts/Videos on Zip / UnZip by Script Task  

No comments:

Post a Comment