Extract the data from different Excel files and based on the file base name load it into the respective txt file and after load archive these files

 We have a requirement that we are getting 4 different types of Excel files with different structures we need to read these files and write these files to respective txt files.

Source directory.

                                                                                                                    

The highlighted string is the base file name.  

We need to load these files into the txt files.   

Afterload we will move these files to the archive directory.  

Afterload the source folder will be empty.    

Let’s see how to achieve.

Below is the package design.

  

Taking the for each loop container to loop the files and storing the file name with full path.   

Storing the file name into the variable.   

In this container, we are taking the script to extract the file base name source and destination file.

Taking some variables that are used in this package.

  

Passing this variable in the script task to assign the source file and destination file and file base name.

In the script task, we are writing the below script.

public void Main()                                 {                                                                                             string product_base_file_nm = Dts.Variables["User::Product_Base_File_Nm"].Value.ToString();                          string fileName = Path.GetFileNameWithoutExtension(Dts.Variables["User::File_Path"].Value.ToString());               if (fileName.Contains(product_base_file_nm))             {                 Dts.Variables["User::Product_File_path"].Value = Dts.Variables["User::File_Path"].Value.ToString();                 Dts.Variables["User::Product_File_des"].Value = Dts.Variables["User::Product_File_des"].Value.ToString() + "\\" + fileName + ".txt";                 Dts.Variables["User::File_Nm"].Value= Dts.Variables["User::Product_Base_File_Nm"].Value.ToString();               }               string Sales_Base_file_nm = Dts.Variables["User::Sales_Base_file_nm"].Value.ToString();                         fileName = Path.GetFileNameWithoutExtension(Dts.Variables["User::File_Path"].Value.ToString());                 if (fileName.Contains(Sales_Base_file_nm))             {                 Dts.Variables["User::Sales_file_path"].Value = Dts.Variables["User::File_Path"].Value.ToString();                 Dts.Variables["User::Sales_file_des"].Value = Dts.Variables["User::Sales_file_des"].Value.ToString() + "\\" + fileName + ".txt";                 Dts.Variables["User::File_Nm"].Value = Dts.Variables["User::Sales_Base_file_nm"].Value.ToString();               }                             string SpecialOffer_Base_file_nm = Dts.Variables["User::SpecialOffer_Base_file_nm"].Value.ToString();               fileName = Path.GetFileNameWithoutExtension(Dts.Variables["User::File_Path"].Value.ToString());                 if (fileName.Contains(SpecialOffer_Base_file_nm))             {                 Dts.Variables["User::SpecialOffer_file_path"].Value = Dts.Variables["User::File_Path"].Value.ToString();                 Dts.Variables["User::SpecialOffer_file_des"].Value = Dts.Variables["User::SpecialOffer_file_des"].Value.ToString() + "\\" + fileName + ".txt";                 Dts.Variables["User::File_Nm"].Value = Dts.Variables["User::SpecialOffer_Base_file_nm"].Value.ToString();             }               string Vendor_base_file_nm = Dts.Variables["User::Vendor_base_file_nm"].Value.ToString();               fileName = Path.GetFileNameWithoutExtension(Dts.Variables["User::File_Path"].Value.ToString());                 if (fileName.Contains(Vendor_base_file_nm))             {                 Dts.Variables["User::Vendor_file_path"].Value = Dts.Variables["User::File_Path"].Value.ToString();                 Dts.Variables["User::Vendor_file_des"].Value = Dts.Variables["User::Vendor_file_des"].Value.ToString() + "\\" + fileName + ".txt";                 Dts.Variables["User::File_Nm"].Value = Dts.Variables["User::Vendor_base_file_nm"].Value.ToString();                             }                   Dts.TaskResult = (int)ScriptResults.Success;           }

 Now we are taking the dataflow and on the Precedence constraints line we are writing the expression to check the file base name. Based on the file name we are redirecting to the data flow task.

  

Read: Precedence constraints in SSIS.

https://bageshkumarbagi-msbi.blogspot.com/2016/05/precedence-constraints-in-ssis.html

For each file we are checking the file and redirecting this into the respective dataflow task.

In the data flow task, we are taking excel as the source and flat file as the destination. Initially, we are doing the hardcoded mapping after that we are creating the connection as dynamic using the expression.

    

 For all data flow tasks, we are doing the same.

After creating the connection string dynamically we need to set the Delay validation property as True.

Read: Delay validation propertyin SSIS.

https://bageshkumarbagi-msbi.blogspot.com/2016/09/delay-validation-properties-in-ssis.html

  

After loading these file we are arching these files into the archive folder.  

We are writing the below code to archive the files.

public void Main()                                 {                        string rootFolderPath = Dts.Variables["User::Source_Files_path"].Value.ToString();             string destinationPath = Dts.Variables["User::archive_path"].Value.ToString();                         string[] fileList = System.IO.Directory.GetFiles(rootFolderPath);                         foreach (string file in fileList)             {                 string fileName = Path.GetFileName(file);                 string destFile = Path.Combine(destinationPath, fileName);                 //moving file                 File.Move(file, destFile);             }                 Dts.TaskResult = (int)ScriptResults.Success;                                 }

Now our package is ready to run.

Source folder.

Destination folder  

Archive folder. 

Now running this package.  

Package executed successfully.

See the source folder.  

Destination folder.  

Archive folder. 

Got the expected result.