How to Extract File Names from a Folder and its Sub Folders and Save in SQL Server Table by using SSIS Package

SSIS Package to Extract File Names from a Folder and its Sub Folders and save in SQL Server Table
Scenario :
Suppose we have the Files in a Folder and its SubFolders as follows..
T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\

Now we want to Load the Files from above Folder and its inside Sub Folders to the below SQL Table "dbo.FilesInfo"

We can accomplish Scenario, by Designing the Package as follows..

------------------------------------------------------------------------------------------------------

Phase-1 : Create the required Variables :

------------------------------------------------------------------------------------------------------

Here we create the 2 Variables as follows..

MainFolder : This is a Static Variable, to which we will assign the Main Folder path, through which we need to loop through to read the Files. This variable we will use as Collection variable in the ForEach Loop Container.

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\

FileFullPath :  The value for this variable is Mapped by ForEach Loop container. In this variable the Full File Name with Path will be stored by ForEach Loop Container during the run time.

The Example value of this Variable : 

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\EmpSrc.txt

------------------------------------------------------------------------------------------------------

Phase-2 : Add the ForEach Loop Container to Control Flow then Set Variables Mapping
------------------------------------------------------------------------------------------------------
A) Add the ForEach Loop Container to Control Flow

B) Next in ForEach Loop Editor>Collection Tab, do the variable Mapping to the Directory from Expressions

C) Next select the Options for..
Files as *.* to read and retrieve all file types.
Retrieve  File Name as "Full Qualified" to retrieve the File Full name with Extension and Forder Path.
Tick Mark the "Traverse SubFolders" to Loop Through Sub Folders as well.

D) Next in ForEach Loop Editor>Variable Mappings Tab, map the "User::FileFullPath" to Store the File Full name with Path, which will assign during run time.

------------------------------------------------------------------------------------------------------

Phase-3 : Add the Execute SQL Task inside the ForEach Loop Container and do the Parameter Mapping

------------------------------------------------------------------------------------------------------

A) Add the Execute SQL Task inside the ForEach Loop Container 

B) In Execute SQL Task > General Tab set the required Connection and SQL Statement
Here I use the below SQL Statement with a Parameter ?
Insert Into [dbo].[FilesInfo] ([FileFullPathName] ) Values (?)

C) In Execute SQL Task > Parameter Mapping do the required Mappings

Here we map the Parameter "User::FileFullPath"  to pass the values to the Field "[FileFullPathName]" used in the above Insert Statement.

D) Finally Save and Run the Package

------------------------------------------------------------------------------------------------------

Output :

Now the Output looks like as follows in SQL Table...

Select * From [FilesInfo]

If you want to Retrieve the Folder and File Names from the File Full Path Column, you can run the below SQL Statement...

SELECT [FileId]
      , Left([FileFullPathName],Len([FileFullPathName])-CharIndex('\',Reverse                                ([FileFullPathName]))+1) As [FolderName]
      , Right([FileFullPathName],CharIndex('\',Reverse([FileFullPathName]))-1) As [FileName]
      ,[FileFullPathName]
      ,[LoadDateTime]
  FROM [dbo].[FilesInfo]
GO

Now the Output looks as follows....

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------