Load the latest file into the database using SSIS Script task

We have a shared folder where we are getting daily n number of files and we need to load theses files to the database on the current date.

 

This the shared folder where we are getting daily files and we need to load the current date files.

 

Today we get two files which we need to load.

Let’s the demo

File format

And Table structure

 

In control flow, I am taking a script task.

I am taking a few variables

Double click on the Script task

Obj_file : this is the object type variable in to store the list of the files.

Source_dir : source directory path.

In C# script editor we are writing below code

string today;   today = DateTime.Now.ToShortDateString();   var directory = new DirectoryInfo(Dts.Variables["User::Source_Dir"].Value.ToString());             FileInfo[] files = directory.GetFiles();             DateTime lastModified = DateTime.MinValue;             ArrayList arr = new ArrayList();            foreach (FileInfo file in files)             {                 if (file.CreationTime.ToShortDateString() == today)                               {                   arr.Add(file.FullName);                 }             }      Dts.Variables["User::Obj_File"].Value = arr;

   

  

In the object we will get the list of files.

Now I am taking Foreach loop container to enumerate the list of files.

 

Now we need to configure the enumeration type.

 

Selecting enumerator type is Foreach from Variable Enumerator and set the variable as object variable. 

Variable Mappings with variable File_name.

Inside the Foreach loop container I am taking data flow task.

 

Taking source as the flat file.

 

Now creating connection

 

Here we need to create the file connection dynamically.

Because file name we are getting for the Foreach loop container.

Taking drive column to add some columns

 

Below column, we are adding 

Taking the OLEDB destination and doing the mapping.

 

Now my package is ready to run.

 

Before running the package, records in the table

Now running this package.

Package execute successfully. See the records in the table.

Number of files loaded.

Get the expected result.