Hope you're doing well. I have a scenario here. As you can see I have a table with two main columns : enter image description here

What I need is to read each "FilePath" which is "Active" right now and then read the Excel files in that path. So I assume that I need at least 2 "For Each loop Container" inside each other. I've already done some part of the scenario : enter image description here

Within the "Execute sql task" I'm retrieving "FilePath" which are active from the table and putting them in an "object" variable (My first variable in SSIS) .

Then I have my second variable in SSIS in which I keep the name of each "FilePath" each time the outer loop is being executed.

At the end I have my third variable which will change each time the inner loop in being executed and it carries the Excel files.

The outer loop must execute to the number of active rows in meta data table and the inner loop must execute to the number of file within the path.

The point is I receive no error after executing the package, but the data won't be inserted in my "OLEDB Destination" I was wondering if you could help me with this issue.

  • Guys , With all respect , I really do need your help and looking forward to hearing from you :) – user157749 Sep 25 '18 at 11:58
  • mssqltips.com/sqlservertip/4165/… Is exactly that what you want? – Luciano Andress Martini Sep 25 '18 at 12:00
  • this is only the part of it .I have different path in which I have different excel files. So i need two loops . the "outer loop" should execute with the number of these paths and the "inner loop" must execute with the number of the excel files in each path . do you know what I mean? – user157749 Sep 25 '18 at 12:04
  • Can you put example of this paths and excel files..., i think this will help if someone who knows how to do that to answer your question... – Luciano Andress Martini Sep 25 '18 at 12:07
  • well , in fact I've already uploaded the related images regarding the matter . Are you able to see the images ? – user157749 Sep 25 '18 at 12:09

When you initially pull the file paths from the ExcelFilePath table, trying returning just names of the folders holding the Excel files that will be used and make sure these end with a \ (i.e. C:\Excel_Exercise\Excel_Source\). You may need to used a T-SQL function like RIGHT/SUBSTRING, but doing this will allow you to load the files using the method outlined below. Also, are you filtering for only "Active" file paths in the SQL query from the Execute SQL Task? As you're probably already doing, make sure to set the DelayValidation and ValidateExternalMetadata properties accordingly.

  • The First Foreach Loop will be a Foreach ADO Enumerator using the object variable from the Execute SQL Task as the source variable. In the Variable Mappings pane, add a string variable that will hold the file directory at index 0.
  • Inside the first Foreach Loop, add another Foreach Loop as a Foreach File Enumerator. On the Collection pane, add an expression for the Directory property that uses the string variable from the outer Foreach loop with the directory. On this Foreach Loop, add a variable in the Variable Mappings pane the will be used to hold the full file path (including file name/extension).
  • Within this Foreach Loop add a Data Flow Task that loads from an Excel Source to the desired destination. On the Connection Manager for the Excel Source, create an expression for the ExcelFilePath and add the variable holding the complete file path and name/extension.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy