Hi 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=\"Excel 8.0;HDR=YES\";" When I run I get this error TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error at Data Flow Task [SSIS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C. Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation. Error at Package [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.". (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ BUTTONS: OK ------------------------------ Help pleaseRednaw

Can you try to give a correct default value for the variable FileName?Nitesh Rai- Please mark the post as answered if it answers your question

Here is a post that shows creating a dynamic flat file connection: http://sqlsafety.blogspot.com/2010/09/ssis-dynamic-connections.html I understand that this is a bit of a variation, but the underlying steps are still the same. David Dye http://sqlsafety.blogspot.com/

Thanks for the answer. I added a default value to the Foreach loop connection variable. The value was simply z:\files\test.xlsx. This time I got this errormsg. Error: Failed to lock variable "User::FileName1" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". Any ideas?Rednaw

Thanks for the link to the blog I haven´t had enough time to test it, but it lookes like it suites my issue well. Rednaw

Hi Rednaw See this blog http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html it has a step by step to loop through excel files and sheets you are on the right track, the trick for it in the design time only is ti point the current file name to a SAMPLE_Excel.xls file , the DELAY VALIDATION for the Excel connection must be TRUE and any other object related to excel, at RUN time the FILEName will point to the right folder (ToBeProcessed) and to the right files, see the blog its all in details good luckSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Also take a look here: http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx regards, PedroVisit My Business Intelligence Blog - If your question is answered, please mark as answered.

Hi Pedro, You links really helpful, my enviroment is SQL 2008, how should i change the VB.net code to suite VS2008? I get this error message for the CurrentTable define in the code : Value of type '1-dimensional array of String' cannot be converted to 'String'. Can you show me step by step for SQL 2008 in VS 2008? Thanks. Regards, Sim

Hi Rednaw, I had the same problem while using For Each Loop Container. Then I gave the value of the variable, the file path of one of the excel sheets in the folder. After that it worked fine for me and the container was able to loop through all the excel sheets in the folder. Why don't you give this a try? Hope this will work for you.. Cheers..Sharath