How to import files week by week using SSIS?

2
votes

I want to load files in SQL server database on weekly basis. Each file name contains date on it. Currently, I am using Foreach Loop Container to get the file name and stored it in table. Table contains 3 columns FileName, Date and Week. After loading FileName using Execute SQL Task I extract Date and Week from the FileName and Populate Date and Week column. Then I use Execute SQL Task to SELECT all table date ORDER BY Date and Week and store it into object variable. Finally, I use Foreach Loop Container to load actual files in date order using ADO Enumerator and object variable. This works fine. However, I want to load files on Weekly basis. For an example all the files which has week 15 in the table should loaded first. Then it should load load all the files of week 16 and so on. The reason I want to load like this is after loading one week of files I want to process it using some stored procedure.

enter image description here

2
sql-serverssis
Do you mean to limit them to the current week or preserve the order in case week 15 and 16 are both in the folder/set? I was initially going to comment that because you are ordering by date... you would already be getting the weeks processed in order no? - TEEKAY
It should load the data but on weekly basis. ORDER BY Date and Week because I can have more than one years of files - Jay Desai
What is that actual problem? What you've explained should work as you describe - Nick.McDermaid

2 Answers

0
votes

I think the problem can be solved by making two edits:

Loop over weeks

  1. Add an Execute SQL Task that retrieve the Distinct Weeks from the table
  2. Add a foreach loop container to loop over weeks
  3. inside the foreach loop add an Execute SQL Task that retrieve the rows based on the current week
  4. Use another foreach loop container to loop over result

Ordered results

You can simply add an ORDER BY clause inside the Execute SQL Task to get an ordered resultset.

0
votes

This is a limitation of the ForEach loop enumerators - there is no way to load files in a sorted/ordered manner. If you want to load files in such a manner then there are two ways to do this:

  1. Purchase an expensive package of components from third party vendors that provide a ForEach loop enumerator that can process files in a sorted/ordered manner
  2. Do it yourself manually.

For option two, you will need to perform the following steps:

  1. Create a ForEach File loop enumerator scan the folder for all files and insert the file names into a database table.
  2. Create an Execute SQL Task that will SELECT all file names, ORDERED BY file name. You can add constraints in the WHERE clause to control the date range of files that you want to process.
  3. Load the result set into a variable of type Object
  4. Create a ForEach ADO loop enumerator to loop through each file name that is stored in the object.
  5. Place a data flow in the loop and then process the files.