Load the Latest Modified File into the database using SSIS Script task

We have a requirement like to load the last modified file into the database. For such type of requirement we will use the script task to identify the last modified file and we need to load this file into the database using data flow task.

See the demo

We have a source directory in that we have n number of files we need to load only last updated file.

 

In this folder “7_20190725195648.csv” file is the latest modified file. We need to load this file.

Taking the script task

 

Taking two variables

In script task we write the logic to get the latest modified file.

string[] files = System.IO.Directory.GetFiles(Dts.Variables["User::Source_Dir"].Value.ToString());             System.IO.FileInfo file;             DateTime lastDate = new DateTime();             string Latest_File = string.Empty;             foreach (string File_Name in files)             {                 file = new System.IO.FileInfo(File_Name);                 if (file.LastWriteTime > lastDate)                 {                     lastDate = file.CreationTime;                     Latest_File = File_Name;                 }             }        Dts.Variables["User::File_Name"].Value = Latest_File;

 

Now 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 from the script task.

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 this package records in the table.

Now running this package.

Now records in the table.

Get the expected result.