Foreach From Variable Enumerator in Foreach loop container in SSIS

This enumerator is used to enumerate the data inside a Variable. We can use this enumerator to loop over the Object variable to get data from that object. For example, to iterate data coming from Arrays, Array Lists, etc. Array can be loaded with .NET scripts through Array or ArrayList or similar collection structures and it should be loaded into an OBJECT type variable in the package.

See the example where we use this.

We have a shared folder where we are getting daily n number of files and we need to load theses files to the database on the current date.

This the shared folder where we are getting daily files and we need to load the current date files.

Today we get two files which we need to load.

File format

 

And Table structure

In control flow I am taking a script task.

I am taking a few variables

Double click on the Script task 

Obj_file : this is the object type variable in to store the list of the files.

Source_dir : source directory path.

In C# script editor we are writing below code

string today;   today = DateTime.Now.ToShortDateString();   var directory = new DirectoryInfo(Dts.Variables["User::Source_Dir"].Value.ToString());             FileInfo[] files = directory.GetFiles();             DateTime lastModified = DateTime.MinValue;             ArrayList arr = new ArrayList();            foreach (FileInfo file in files)             {                 if (file.CreationTime.ToShortDateString() == today)                               {                   arr.Add(file.FullName);                 }             }      Dts.Variables["User::Obj_File"].Value = arr;

                   

In the object we will get the list of files.

Now I am taking Foreach loop container to enumerate the list of files.

 

Now we need to configure the enumeration type.

 

Selecting enumerator type is Foreach from Variable Enumerator and set the variable as object variable.

Variable Mappings with variable File_name.

Inside the Foreach loop container I am taking data flow task.

 

Taking source as the flat file.

Now creating connection

  

Here we need to create the file connection dynamically.

 

Because file name we are getting for the Foreach loop container.

Taking drive column to add some columns

  

Below column we are adding

 

Taking the OLEDB destination and doing the mapping.

 

Now my package is ready to run.

Before running the package, records in the table

 

Now running this package.

  

Package execute successfully. See the records in the table.

 

Number of files loaded.

  

Get the expected result.

Load the latest file into the database using SSIS Script task

We have a shared folder where we are getting daily n number of files and we need to load theses files to the database on the current date.

 

This the shared folder where we are getting daily files and we need to load the current date files.

 

Today we get two files which we need to load.

Let’s the demo

File format

And Table structure

 

In control flow, I am taking a script task.

I am taking a few variables

Double click on the Script task

Obj_file : this is the object type variable in to store the list of the files.

Source_dir : source directory path.

In C# script editor we are writing below code

string today;   today = DateTime.Now.ToShortDateString();   var directory = new DirectoryInfo(Dts.Variables["User::Source_Dir"].Value.ToString());             FileInfo[] files = directory.GetFiles();             DateTime lastModified = DateTime.MinValue;             ArrayList arr = new ArrayList();            foreach (FileInfo file in files)             {                 if (file.CreationTime.ToShortDateString() == today)                               {                   arr.Add(file.FullName);                 }             }      Dts.Variables["User::Obj_File"].Value = arr;

   

  

In the object we will get the list of files.

Now I am taking Foreach loop container to enumerate the list of files.

 

Now we need to configure the enumeration type.

 

Selecting enumerator type is Foreach from Variable Enumerator and set the variable as object variable. 

Variable Mappings with variable File_name.

Inside the Foreach loop container I am taking data flow task.

 

Taking source as the flat file.

 

Now creating connection

 

Here we need to create the file connection dynamically.

Because file name we are getting for the Foreach loop container.

Taking drive column to add some columns

 

Below column, we are adding 

Taking the OLEDB destination and doing the mapping.

 

Now my package is ready to run.

 

Before running the package, records in the table

Now running this package.

Package execute successfully. See the records in the table.

Number of files loaded.

Get the expected result.

Load the Latest Modified File into the database using SSIS Script task

We have a requirement like to load the last modified file into the database. For such type of requirement we will use the script task to identify the last modified file and we need to load this file into the database using data flow task.

See the demo

We have a source directory in that we have n number of files we need to load only last updated file.

 

In this folder “7_20190725195648.csv” file is the latest modified file. We need to load this file.

Taking the script task

 

Taking two variables

In script task we write the logic to get the latest modified file.

string[] files = System.IO.Directory.GetFiles(Dts.Variables["User::Source_Dir"].Value.ToString());             System.IO.FileInfo file;             DateTime lastDate = new DateTime();             string Latest_File = string.Empty;             foreach (string File_Name in files)             {                 file = new System.IO.FileInfo(File_Name);                 if (file.LastWriteTime > lastDate)                 {                     lastDate = file.CreationTime;                     Latest_File = File_Name;                 }             }        Dts.Variables["User::File_Name"].Value = Latest_File;

 

Now I am taking Data flow task

Taking source as the flat file.

Now creating connection

Here we need to create the file connection dynamically.

 

Because file name we are getting from the script task.

Taking drive column to add some columns

Below column, we are adding

Taking the OLEDB destination and doing the mapping.

Now my package is ready to run.

 

Before running this package records in the table.

Now running this package.

Now records in the table.

Get the expected result.