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.
