SSIS -How To Get Most Recent File from Directory And Load To a Table [ SSIS Script Task ]


We get the files in our Source Folder all day long. Each file is appended copy of previous file. We want to create a SSIS Package that will load only the most recent file to our table.

Solution :

We will be using Script task to find out the name of the most recent file and Data Flow Task to load that file to our table.Test_File_20131226.txt is the file that should be read as it is most recent file.

Step 1:
Create a variable VarFolderPath that will contain the folder path in which our files exist and second variable with name VarFileName which will hold the value of most recent File Name.

Step 2:
Drag Script Task to Control Flow Pane and Provide the variables to it as shown 

Click on Edit Script and write below script. I have only added the code which is in Red. I have included just for debugging.

   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_2650e9fc7f2347b2826459c2dce1b5be.csproj
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

        #region VSTA generated code
        enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

          public void Main()
     // TODO: Add your code here
            var directory= new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());
            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MinValue;

            foreach (FileInfo file in files)
                if (file.LastWriteTime > lastModified)
                    lastModified = file.LastWriteTime;
                    Dts.Variables["User::VarFileName"].Value = file.ToString();


            Dts.TaskResult = (int)ScriptResults.Success;

Step 3:
Let's run our SSIS Package to make sure that our script is returning us expected file name( Most recent modified by)

Step 4 :
Next step will be to load this file to our destination table. Bring Data Flow Task to Control Flow Pane and connect Script Task to it.Drag Flat File Source inside Data Flow Task and make connection to any file in the Source Folder as all the files have same structure.

Step 5:
As latest file name will be changing, Let's configure our Flat File Connection Manager to use VarFileName variable.

Final Output:
Drag Multicast Transformation in Data Flow Task and connect Flat File Source to it. Double clicking on green line between them and put Data Viewer.  I am using Multicast Transformation just for test purpose. In real scenario you will be using OLE DB Destination if you are loading data to table or any other destination depending upon your requirements.

The latest file is read from folder and display by using Data Viewer.


  1. Replies
    1. IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble.Final Year Projects for CSE

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining .

      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

      The Angular Training covers a wide range of topics including Angular Directives, Angular Services, and Angular programmability.Angular Training

  2. Thanks a lot!!! YOu are the best.

  3. This comment has been removed by the author.

  4. Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai

    aluminium scaffolding dealers in chennai

  5. Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai

    aluminium scaffolding dealers in chennai

  6. Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai

    aluminium scaffolding dealers in chennai

  7. Sorry got well.. thanks

  8. Thank you so much for practically explaining things about SSIS and SQL.

    SSIS Postgresql Write

  9. The information you've shared in this blog is remarkable. Thanks for sharing such quality information.
    aluminium scaffolding dealers in chennai
    scaffolding dealers in chennai

  10. may i get the source of the above

  11. This is very good information, Thank you

  12. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    court marriage in delhi ncr
    court marriage in delhi
    court marriage in noida
    court marriage in ghaziabad
    court marriage in gurgaon
    court marriage in faridabad
    court marriage in greater noida
    name change online
    court marriage in chandigarh
    court marriage in bangalore

  13. I really feel there is a need to provide the best information about SSIS and its uitlities that produces the best results.

    SSIS Postgresql Write

  14. where we can download above example text files

  15. Thank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.
    CRM with Invoicing

  16. How does it change the filename variable. I can't seem to get it to update, it's staying blank

    1. I'm getting the same as the pictures all the way through to the end of step 5, The picture shows that when you evaluate the expression, it only shows the folder path, mine does the same. But the above shows data is being transferred. Mine gets an error "[Connection manager "Excel Connection Manager 1"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

  17. Thank you very much for this, I'm slightly knew to MSBI and I recently put this into good use at my work.

    Appreciation from South Africa (2020)

  18. An ever increasing number of private ventures today are utilizing on the web professional listings to their advantage. local classifieds

  19. Great post. Thank you. It helped me out !

  20. The C# code could have been better
    Something simple like this

    var filesInOrder = new DirectoryInfo(path).GetFiles()
    .OrderByDescending(f => f.LastWriteTime)
    .Select(f => f.Name)

  21. Hi,
    This is very useful. I have an similar kind of query, How can I download most recent file from Azure Blob Storage folder?

  22. The writer has outdone himself this time. It is not at all enough; the website is also utmost perfect. I will never forget to visit your site again and again. convert pdf to png

  23. When I read an article on this topic, the first thought was profound and difficult, and I wondered if others could understand.. My site has a discussion board for articles and photos similar to this topic. Could you please visit me when you have time to discuss this topic? 토토커뮤니티

  24. I'm so happy to finally find a post with what I want. 안전놀이터순위 You have inspired me a lot. If you are satisfied, please visit my website and leave your feedback.