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