Load Text Files,Zip them to Folder with Datetime and Delete From Input Directory in SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We get text files in our Input Folder and we need to run our SSIS Package multiple times a day and load those files to table/s and then zip those files in a file with datetime in Archive Folder.

Solution:


We will be using .NET Framework 4.5 and ZipFile class to handle this scenario by using in Script Task in SSIS Package.

Here are the items you will learn in this video

  1. How to read multiple Text files with same metadata and load to SQL Server Table, we will make use of Foreach Loop Container to loop through files
  2. How to Create Package Parameters in SSIS Package for Input Folder and Archive folder path 
  3. How to use Package Parameters in Script Task
  4. How to change .NET Framework in Script task from 4 to 4.5 so we can use ZipFile Class
  5. How to Add reference to Assembly System.IO.Compression.FileSystem
  6. How to Zip the Files to folder with Datetime and then delete them from input Folder in Script Task

Script used in the Script task to Zip the files to Zip File with Datetime and delete from Input Folder


public void Main()
        {
            //set the value to local variables
            string inputfolder=Dts.Variables["$Package::InputFolder"].Value.ToString();
            string archpath = Dts.Variables["$Package::ArchFolder"].Value.ToString() + 
"Sales_" + DateTime.Now.ToString("yyyyMMddHHmmss")+".zip";
           
            //Create Zip File with Datetime and put all the files from input folder
            ZipFile.CreateFromDirectory(inputfolder, archpath);

            //Delete all the files from Input Folder
            DirectoryInfo Dir = new DirectoryInfo(inputfolder);

            foreach (FileInfo File in Dir.GetFiles())
            {
                File.Delete();
            }
            // TODO: Add your code here

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

1 comment:

Subscribe to: Post Comments (Atom)