I´ve seen a few posts on this topic but I still can´t get my simple example to work. What I want to do is to loop thru all Excel files in a folder and put some of the data in my db. It works fine without the Foreach loop container. This is what
I´ve done. First, I´m working on a Windows Server 2008r2 and against a SQL Server 2008r2. The Excel files are Excel 2007.
1) I created a new Integration Services Project.
2) I added a Data flow task to the Control flow tab.
3) I added two connection managers. One for the Excel file source and one for the destination db.
4) I added an Excel Source and a SQL Server destination and connected them. It works fine, I can preview the columns and the mapping is done correct.
When I run it, the db is populated.
5) This is where it fails. I added a Foreach Loop Container and drag the Data flow task into it. In the collection tab I choose Foreach file enumerator and browse to the folder.
5) Under the variable mapping tab I add a variable called FileName under the scope User and inddex 0.
6) Then I flipped to properties for the Excel Connection manager and enter the expression setting. I added a connection property with this expression "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"E
View Complete Post