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

Scenario:

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 Messagebox.show 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
        };
        #endregion

          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();
                }
            }

            MessageBox.Show(Dts.Variables["User::VarFileName"].Value.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.

30 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Sorry got it..works well.. thanks

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

    SSIS Postgresql Write

    ReplyDelete
  4. may i get the source of the above

    ReplyDelete
  5. This is very good information, Thank you

    ReplyDelete
  6. 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

    ReplyDelete
  7. where we can download above example text files

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

    ReplyDelete
    Replies
    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.
      "

      Delete
  9. 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)

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

    ReplyDelete
  11. Great post. Thank you. It helped me out !

    ReplyDelete
  12. 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)
    .ToList();

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

    ReplyDelete
  14. 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

    ReplyDelete
  15. It's truly impressive. I marvel you created such a great write-up. I'm still delighted. Check out the response of these individuals currently. Every person agrees with me. As an individual that can actually associate, I do not wish to conserve praises. You need to constantly be an author. 바카라사이트

    ReplyDelete
  16. Awesome article, it was exceptionally helpful! I simply began in this and I'm becoming more acquainted with it better! Cheers, keep doing awesome! Sharepoint employee directory

    ReplyDelete
  17. site will acknowledge any connect to fill directory classes rather than zeroing in on quality postings
    You can see these new indexes springing up day by day assuming you are a normal peruser of any of the well known SEO discussions.https://iaupa.com/

    ReplyDelete
  18. An ever increasing number of private ventures today are utilizing on the web professional listings to their advantage. https://onohosting.com/

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete


  20. This seems to be the age of the entrepreneur,
    슬롯사이트

    Here I am once again trying to explain things to people.
    슬롯사이트


    ReplyDelete