Get File names from Zip Files and Insert into SQL Server Table - SQL Server Integration Services(SSIS) Tutorial

Scenario:

There are tons of zip files in our Archive folder. we need to  know how many files each zip file contains and names of them. How can we do that in SSIS Package?


Solution:

There can be multiple ways to do this. I thought about using Foreah Loop Container first and then Script Task. Then thought why not to use only Script Task. So here is the things we will be doing in this SSIS Package to get this requirement done.


  1. Create an Package Parameter for ArchiveFolder Path
  2. Inside Script task, we will change the  Dot Net Framework to 4.5 version so we can make sure of System.IO.Compression and System.IO.Compression.FileSytem Assemblies.
  3. Create ADO.NET Connection Manager to the database where we want to log the zip file name and file names inside the zip file.
  4. Use the ADO.NET Connection Manager in script task to insert records into table those we retired from File System for Zip Files.

SQL Server Table Script to Store Zip File Names and files inside them


Create table dbo.ZipFileInfo ( 

id int identity(1,1),
ZipFileName VARCHAR(100),
FileName VARCHAR(100))
go


How to Get File Names for All Zip Files in a Folder in SSIS Package and write them to Table, Script used in Script Task

public void Main()
        {
            //Assign values to local variable from Package Parameters
            string archfolder = Dts.Variables["$Package::ArchFolder"].Value.ToString();

            //Sure ADO.NET Connectionn Manager that we created in SSIS Package
            SqlConnection DBConn = new SqlConnection();
            DBConn = (SqlConnection)(Dts.Connections["DBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);
            //Prepare SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.Connection = DBConn;


            //Get List of all Zip Files from Archive Folder
            string[] files = Directory.GetFiles(archfolder,"*.zip");

            // Display all the files.
            foreach (string zipfile in files)
            {
                 using (ZipArchive archive = ZipFile.OpenRead(zipfile))
                {
                    foreach (ZipArchiveEntry FileEntry in archive.Entries)
                    {
                        sqlCmd.CommandText = "Insert into dbo.ZipFileInfo Values('"
                        zipfile + "','" + FileEntry.FullName + "')";
                       // MessageBox.Show(sqlCmd.CommandText);

                        sqlCmd.ExecuteNonQuery();
                        //MessageBox.Show(zipfile+":::"+FileEntry.FullName);
                    }
                } 
            }
            DBConn.Close();

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



Extract File Names from Zip Files and Insert into SQL Server Table in SSIS Package - SSIS Tutorial

No comments:

Post a Comment