0

I have a folder which contains files like:

A_ddmmyyyy, b_ddmmyyyy and c_ddmmyyyy.

I need to read all these files for a date and again all these files for the next date present in the same folder. Also the number of files present in the folder varies (may contain data for three days or of five dates), but the date on the folder remains the same.

Is it possible?

fantaghirocco
  • 4,761
  • 6
  • 38
  • 48
atul bathla
  • 1
  • 1

2 Answers2

0

You can create a Foreach Loop Container, inside the container create a data flow task that processes all the files in a specific folder. You create a flat file connection with the needed delimiter, and a variable that has the folder path for your files. And then as a last step to your tasks, create a File System Task to move the file to a processed or completed folder so that the your main folder empties out once the files are processed .

Jason B.
  • 315
  • 1
  • 12
  • Thnx Jason, but the data present in Each file is different – atul bathla Jul 27 '16 at 14:42
  • So if your saying the files have to be processed in order, A first and then B and then C, for a particular date. Create a Foreach Loop Container that dumps the file names to a table, then apply your logic to iterate through the list for which files to process and what order (either via SQL or via Sort Transformation in ssis). This Container gets executed first, then the Foreach Loop Container I mention above second. – Jason B. Jul 27 '16 at 14:50
  • If your saying the files have to be processed into different tables based on the file names, is there some agreed upon naming convention so that you can pick out which files go to which table? If so, create a Foreach Loop Container that dumps the file names to a table, then apply your logic to iterate through the list for which files to process into their related tables (either via SQL or via Sort Transformation in ssis). This Container gets executed first, then the Foreach Loop Container I originally mention above, second. – Jason B. Jul 27 '16 at 14:53
0

File name doesn't matter, you have to be certain how many kinds of schemas (number of columns, column names, types) of files in the folder. Say you have 3 schemas, then you will need to define 3 type of flat file connections. There are many ways to do the job, the easiest of i can think of is to use powershell to separate files of different schema into different folders, you have to know what file names are mapped to what schema, there may be a pattern or business rule. You then put your powershell in a execute process task to run the script. Then the following is simple, for each folder you create a package, inside is a for each container where you loop through the folder to load each file. Or you can have one package with three for each loop container to do the job.

thotwielder
  • 1,563
  • 7
  • 44
  • 83