Filter the file name using regular expression and load it into the database in SSIS

We have a source folder where we are getting n type of file like (sales, product and admin files etc.). We need to load only the sales files into the database. Based on the file we need to identify the type of file.

Like: sales_10062019_1 is for sales file. See the below

We need to load only sales files.

I am taking Script task. Taking some variables.

RegexFilter : ^(sales).*\.csv$

Obj_File_list : system.object

FilePath : Source folder path

We need to write below C# code.

string regexPattern = Dts.Variables["User::RegexFilter"].Value.ToString();             bool fireAgain = true;             var directory = new DirectoryInfo(Dts.Variables["User::filepath"].Value.ToString());             FileInfo[] files = directory.GetFiles();                       ArrayList arr = new ArrayList();             Regex FileFilterRegex = new Regex(regexPattern, RegexOptions.IgnoreCase);             foreach (FileInfo file in files)             {                 if (FileFilterRegex.IsMatch(file.Name))                 {                     arr.Add(file.FullName);                     // Add for testing purpose only                               Dts.Events.FireInformation(0, "SCR_Get_Sales_Files", file.FullName, string.Empty, 0, ref fireAgain);                 }             }             Dts.Variables["User::Obj_File_List"].Value = arr;

Files are stored on the array list which are going to be loaded.

Now I am taking Foreach loop container and selecting Foreach for variable Enumerator and assigning the object variable for enumeration.

Now I am assigning the Variable mapping.

Clicking ok.

Taking Source as the flat file source. Creating the flat file connection manager and providing the connection string as the variable file name.

Taking destination as OLEDB destination.

Now package is ready to execute.

See the source folder.

Expected file need to be loaded.

See the records in the table.

Now running this package.

Package executed successfully. Looking the script task log

Let’s see the records in the table.

List of file’s are loaded.

Expected files are loaded.