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.

96 comments:

  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

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

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

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

    SSIS Postgresql Write

    ReplyDelete
  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

    ReplyDelete
  10. may i get the source of the above

    ReplyDelete
  11. This is very good information, Thank you

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  14. where we can download above example text files

    ReplyDelete
  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

    ReplyDelete
  16. 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
  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)

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

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

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

    ReplyDelete
  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?

    ReplyDelete
  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

    ReplyDelete
  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? 토토커뮤니티

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

    ReplyDelete
  25. I've been searching for hours on this topic and finally found your post. Tàixỉu, I have read your post and I am very impressed. We prefer your opinion and will visit this site frequently to refer to your opinion. When would you like to visit my site?

    ReplyDelete
  26. You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this. 메이저놀이터

    ReplyDelete
  27. I blog often and I truly appreciate your content.
    야설
    Feel free to visit my blog :
    야설

    ReplyDelete
  28. This great article has truly peaked my interest.
    일본야동
    Feel free to visit my blog : 일본야동

    ReplyDelete
  29. I’m going to bookmark your site and keep checking for new details about once per week.
    국산야동
    Feel free to visit my blog : 국산야동

    ReplyDelete
  30. I subscribed to your Feed too.
    일본야동
    Feel free to visit my blog : 일본야동

    ReplyDelete
  31. Hi there! This article could not be written much better!
    야설
    Feel free to visit my blog : 야설

    ReplyDelete
  32. 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
  33. 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
  34. Your post is very interesting to me. Reading was so much fun. I think the reason reading is fun is because it is a post related to that I am interested in. Articles related to 메이저놀이터순위 you are the best. I would like you to write a similar post about !

    ReplyDelete
  35. I’m not sure exactly why but this weblog is loading incredibly slow for me. Is anyone else having this problem or is it a problem on my end? I’ll check back later on and see if the problem still exists. 안전놀이터순위

    ReplyDelete
  36. Hello, I am one of the most impressed people in your article. 토토사이트 I'm very curious about how you write such a good article. Are you an expert on this subject? I think so. Thank you again for allowing me to read these posts, and have a nice day today. Thank you.

    ReplyDelete
  37. Hello there! Quick question that’s completely off topic.
    Do you know how to make your site mobile friendly? My website looks weird when viewing from my iphone.
    I’m trying to find a template or plugin that might
    be able to resolve this issue. If you have any recommendations, please share.
    Thank you!

    website:토토

    ReplyDelete

  38. This site seems to inspire me a lot. Thank you so much for organizing and providing this quality information in an easy to understand way. I think that a healthy era of big data can be maintained only when such high-quality information is continuously produced. And I, too, are working hard to organize and provide such high-quality information. It would be nice to come in once and get information.

    Also visit my site:온라인카지노

    ReplyDelete
  39. There must have been many difficulties in providing this information.우리카지노 Nevertheless, thank you for providing such high-quality information.

    ReplyDelete
  40. When I read your article on this topic, the first thought seems profound and difficult. There is also a bulletin board for discussion of articles and photos similar to this topic on my site, but I would like to visit once when I have time to discuss this topic. 안전토토사이트

    ReplyDelete
  41. I visited last Monday, and in the meantime, I came back in baccarat anticipation that there might be other articles related to I know there is no regret and leave a comment. Your related articles are very good, keep going!!

    ReplyDelete
  42. First of all, thank you for your post. 바카라사이트 Your posts are neatly organized with the information I want, so there are plenty of resources to reference. I bookmark this site and will find your posts frequently in the future. Thanks again ^^

    ReplyDelete
  43. Are you the one who studies this subject?? I have a headache with this subject.우리카지노Looking at your writing was very helpful.

    ReplyDelete
  44. I like the helpful info you provide in your articles. I’ll bookmark your blog and check again here frequently. I’m quite sure I’ll learn plenty of new stuff right here! Good luck for the next. 먹튀검증업체

    ReplyDelete
  45. I'm looking for a lot of data on this topic. The article I've been looking for in the meantime is the perfect article. Please visit my site for more complete articles with him! casino trực tuyến

    ReplyDelete
  46. Excellent read, I just passed this onto a friend who was doing a little research on that. And he actually bought me lunch as I found it for him smile Therefore let me rephrase that: Thank you for lunch. 메이저사이트

    ReplyDelete
  47. 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
  48. What a nice post! I'm so happy to read this. 안전놀이터모음 What you wrote was very helpful to me. Thank you. Actually, I run a site similar to you. If you have time, could you visit my site? Please leave your comments after reading what I wrote. If you do so, I will actively reflect your opinion. I think it will be a great help to run my site. Have a good day.

    ReplyDelete
  49. I was impressed by your writing. Your writing is impressive. I want to write like you.안전놀이터 I hope you can read my post and let me know what to modify. My writing is in I would like you to visit my blog.

    ReplyDelete
  50. Your ideas inspired me very much. 메이저토토사이트모음 It's amazing. I want to learn your writing skills. In fact, I also have a website. If you are okay, please visit once and leave your opinion. Thank you.

    ReplyDelete
  51. I finally found what I was looking for! I'm so happy. 안전한놀이터 Your article is what I've been looking for for a long time. I'm happy to find you like this. Could you visit my website if you have time? I'm sure you'll find a post of interest that you'll find interesting.

    ReplyDelete
  52. From one day, I noticed that many people post a lot of articles related to 온라인슬롯 . Among them, I think your article is the best among them!!I

    ReplyDelete
  53. What a post I've been looking for! I'm very happy to finally read this post. 안전놀이터 Thank you very much. Can I refer to your post on my website? Your post touched me a lot and helped me a lot. If you have any questions, please visit my site and read what kind of posts I am posting. I am sure it will be interesting.

    ReplyDelete
  54. I no uncertainty esteeming each and every bit of it. It is an amazing site and superior to anything normal give. I need to grateful. Marvelous work! Every one of you complete an unfathomable blog, and have some extraordinary substance. Keep doing stunning 메이저사이트순위

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

    ReplyDelete
  56. This is the perfect post.메이저토토사이트 It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.

    ReplyDelete
  57. I think your website has a lot of useful knowledge. I'm so thankful for this website.
    I hope that you continue to share a lot of knowledge.
    This is my website.
    머니상

    ReplyDelete
  58. I had a lot of fun at this Olympics, but something was missing. I hope there's an audience next time.안전토토사이트

    ReplyDelete
  59. You made some good points there. I did a Google search about the topic and found most people will believe your blog. 메이저사이트

    ReplyDelete
  60. I was impressed by your writing. Your writing is impressive. I want to write like you.안전놀이터 I hope you can read my post and let me know what to modify. My writing is in I would like you to visit my blog.

    ReplyDelete
  61. Mega Game Online Slot Game https://megagame.vegas/ The newest website 2021, the number 1 slot website

    PG SLOT online slots game https://pgslot-games.co/ Sign up for a new account, get a 100% bonus, 1st place

    PG SLOT online slots game https://pgslot-games.com/ Sign up for a new account, get a 100% bonus, 1st place

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

    ReplyDelete


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

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


    ReplyDelete