How to store file names in SQL Server Table by using SSIS Package

Scenario:

In this blog post we are going to learn how to read the file name from a Folder and Sub Folders and insert into a SQL Server Table. This can be helpful for Audit and to know how many files we have in folders.

Solution:

We will be using For-each loop container to get the files information. Let's start step by step 

Step 1:

To save the file name we need to have a SQL Server table. Let's create a SQL Server table by using below script.

CREATE TABLE dbo.FileInformation
  (
     FileID   INT IDENTITY(1, 1),
     FileName VARCHAR(250)
  )

Step 2:

Create an SSIS Package by using BIDS or SSDT. Create a variable as shown below that is holding your files and also have sub folders with files.

Fig 1: Create FolderPath variable in SSIS Package

FileNamewithPath variable will be used in Foreach Loop Container to hold the value of file name with path that we will insert into our SQL Server table.

 Step 3:

Drag For-each loop container from SSIS Toolbox on Control Flow Pane and configure as shown below. If you are only interested to save file name then use Name only and if you are interested to save file name with extension then use Name and Extension. As I want to save file name with folder , I have chosen Fully qualified in #5.
Fig 2: Configure Foreachloop Container to read the file names with path

Map the value read by Foreach loop container to FileNamewithPath varible.
Fig 3: Map the value to Variable in Foreach loop container

Step 4:

Create OLE DB Connection to the database where you have created dbo.FileName table.

Step 5:

As now we can read the file name with path by using Foreach loop and values will be saved in variable for each iteration , we can save the value of variable in our SQL Server Table. To save the variable value in SQL Server Table, we can use Execute SQL Task. Bring the Execute SQL Task inside the Foreach Loop container and configure as shown below.

 Fig 4: Configure Execute SQL Task to insert variable value in SQL Server Table in SSIS Package


Map the variable to Execute SQL Task insert statement.
Fig 5: Map variable in Execute SQL Task in SSIS Package


Output:

Our package is ready. Let's run our SSIS Package and see if the file names are saved in SQL Server table from folder/s.

 Fig 6: SSIS Package to load file names from folder/s in a SQL Server Table


Output from SQL Server Table
 Fig 7: File Names in SQL Server table stored by SSIS Package

As we can see that the file names with folder information is successfully inserted in SQL Server table.


Video Tutorial for this post

47 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. This comment has been removed by the author.

    ReplyDelete
  3. คาสิโนออนไลน์ที่น่าเชื่อถือและมีความเป็นมืออาชีพที่สุดในตอนนี้
    โปรโมชั่นGclub ของทางทีมงานตอนนี้แจกฟรีโบนัส 50%
    เพียงแค่คุณสมัคร สล็อตออนไลน์ กับทางทีมงานของเราเพียงเท่านั้น
    ร่วมมาเป็นส่วนหนึ่งกับเว็บไซต์คาสิโนออนไลน์ของเราได้เลยค่ะ
    สมัครสล็อตออนไลน์ >>> Goldenslot
    สนใจร่วมสนุกกับ คาสิโนออนไลน์ คลิ๊กได้เลย
    มีทั้งคาสิโนออนไลน์ หวยออนไลน์ ฟุตบอลออนไลน์ สล็อตออนไลน์ และอื่นๆอีกมากมาย

    ReplyDelete
  4. Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
    web designing classes in chennai | web designing training institute in chennai
    web designing and development course in chennai | web designing courses in Chennai
    best institute for web designing in chennai | web designing course with placement in chennai
    Web Designing Class
    web designing course
    best institute for web designing

    ReplyDelete
  5. This is a great inspiring article. I am pretty much pleased with your good work. You put really very helpful information. Keep it up. Keep blogging. Looking forward to reading your next post.
    Best Wedding Photographers | Wedding Photographers in Rohini | Wedding Photographers in Pitampura | Wedding Photographers in Delhi

    ReplyDelete
  6. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    Thanks...
    web development company sydney

    ReplyDelete
  7. This article is an appealing wealth of informative data that is interesting and well-written. I commend your hard work on this and thank you for this information. You’ve got what it takes to get attention.WordPress Plugins

    ReplyDelete
  8. Great Article. Thank you for sharing! Really an awesome post for every one.
    Digital Marketing Course In Kolkata
    Web Design Course In Kolkata

    ReplyDelete
  9. Its a great pleasure reading this post. Want to share with another useful article
    https://cgifurniture.com/photorealistic-product-rendering-services-online-shopping/

    ReplyDelete
  10. Nice article,i really admire after reading this blog. oracle training in chennai

    ReplyDelete
  11. I am incapable of reading articles online very often, but I’m happy I did today. It is very well written, and your points are well-expressed. I request you warmly, please, don’t ever stop writing. where to buy cbd

    ReplyDelete
  12. What a fantabulous post this has been. Never seen this kind of useful post. I am grateful to you and expect more number of posts like these. Thank you very much. what is cbd vape oil

    ReplyDelete
  13. Wonderful article. Fascinating to read. I love to read such an excellent article. Thanks! It has made my task more and extra easy. Keep rocking. cbd online

    ReplyDelete
  14. I have been checking out a few of your stories and i can state pretty good stuff. I will definitely bookmark your blog cbd cheap

    ReplyDelete
  15. Ensure you get your store out there. Find significant blog entries or become a confided in individual from a discussion applicable to your items. On the off chance that individuals don't have the foggiest idea about your store exists they can't accepting from it.besimple.com/

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

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

    ReplyDelete
  18. Merely a smiling visitant here to share the love (:, btw outstanding style. Fait au Quebec

    ReplyDelete
  19. Infycle Technologies offers the Best Data training in chennai and is widely known for its excellence in giving the best Data Science Certification course in Chennai. Providing quality software programming training with 100% placement & to build a solid career for every young professional in the software industry is the ultimate aim of Infycle Technologies. Apart from all, the students love the 100% practical training, which is the specialty of Infycle Technologies. To proceed with your career with a solid base, reach Infycle Technologies through 7502633633.

    ReplyDelete
  20. Grab the extraordinary Oracle Course with PLSQL from Infycle Technologies, the best software training institute in Chennai. Infycle offers the Best Oracle PLSQL Training in Chennai, with various IT demanding courses such as Big Data, Python, DevOps, Selenium, Full-Stack development, etc., in complete hands-on practical training with professional tutors in the field. In addition to that, the mock interviews will be done for the candidates so that they can face the interviews with total confidence. To have all these within your hands, call 7502633633 for a free demoBest Oracle PLSQL Training in Chennai | Infycle Technologies

    ReplyDelete
  21. Infycle Technologies, the best software training institute in Chennai. offers the leading Python course in Chennai for tech professionals, freshers, and students at the best offers. In addition to the Python course, other in-demand courses such as Data Science, Cyber Security, Selenium, Oracle, Java, Power BI, Digital Marketing also will be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.

    ReplyDelete
  22. The Blog Your Shared is Very Helpful, This is So good hope we get More blogs on your site like this, This blog is so infomative and exited for your next blog

    Escorts Service in Mahipalpur.

    ReplyDelete
  23. Most of the time I don’t make comments on websites, but I'd like to say that this article really forced me to do so. Really nice post! cbd gummies no corn syrup how to make

    ReplyDelete
  24. Attractive, post. I just stumbled upon your weblog and wanted to say that I have liked browsing your blog posts. After all, I will surely subscribe to your feed, and I hope you will write again soon! name your business

    ReplyDelete
  25. A great content material as well as great layout. Your website deserves all of the positive feedback it’s been getting. I will be back soon for further quality contents. creative business names

    ReplyDelete
  26. Infycle Technologies, the top software training institute and placement center in Chennai offers the Best Digital Marketing Course in Chennai | Infycle Technologies for freshers, students, and tech professionals at the best offers. In addition to Digital Marketing, other in-demand courses such as DevOps, Data Science, Python, Selenium, Big Data, Java, Power BI, Oracle will also be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.

    ReplyDelete
  27. mmm.. good to be here in your article or post, whatever, I think I should also work hard for my own website like I see some good and updated working in your site.
    Hasnain

    ReplyDelete
  28. Finish the Selenium Training in Chennai from Infycle Technologies, the best software training institute in Chennai which is providing professional software courses such as Data Science, Artificial Intelligence, Java, Hadoop, Big Data, Android, and iOS Development, Oracle, etc with 100% hands-on practical training. Dial 7504633633 to get more info and a free demo and to grab the certification for having a peak rise in your career.

    ReplyDelete
  29. Great article with excellent idea!Thank you for such a valuable article. I really appreciate for this great information.. check this site

    ReplyDelete
  30. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post. umrah packages

    ReplyDelete