InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 18:44:35 GMT20http://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxHelloI have found the reason why I was getting errors. I had been able to get the for each loop workong on normal flat file so i assumed that the connection string expression wouild be the same. I know now that it is not. For Excel the connection String expresion in the connection manager properties need to look like this. Two variables are needed one is the file name and the second is a variable that contains the extended conenction details."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelImput] + ";Extended Properties=\"" + @[User::extendedPro] + "\""Thanks for all the help, problem solved :)OlafFri, 04 Jul 2008 00:12:16 GMTolafdedighttp://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxi am using the Connection String from the drop down then set it to the variable as far as the variable goes (the one that contains the file name) I create it in the foreachloop editor wherer is says variable mappings, I select new variable - will try craeting it at the package levelThu, 03 Jul 2008 08:46:39 GMTolafdedighttp://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxTry creating your variables at the package level. You may need to delete your variables and recreate them with the mouse focus on the background of your package.What property of the connection manager are you setting in the Expressions property? You should be using ConnectionString.Thu, 03 Jul 2008 08:02:44 GMTkeymoohttp://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxThanks for the help so far, i have not used variables before in the SSIS packages before, so this is what I have done (after going through a few other posts as well) I have first now tried a for each loop created a variable in the for each loop that then should be used to cycle through the files in that folder. but when i update the excel connection manager with the expression "conenction String and set it to the variable I get the following 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.So i open up the excel connection manager but the location is now blank and will not run - any hintsThu, 03 Jul 2008 07:48:19 GMTolafdedighttp://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxYou could put the name of the file into a Package Variable and then reference the package variable to set the Excel Source using an Expression in the connection manager.Thu, 03 Jul 2008 06:50:14 GMTkeymoohttp://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxHello Thanks for the link, the controlflow can let me monitor the folder, and as far as I can tell returns the name of the file for later use, how do i then go about using that filename to import the excel file, how to i modify the excel source in the data flow items to then import that file.Thu, 03 Jul 2008 05:48:54 GMTolafdedighttp://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxYou could use the File Watcher TaskSee http://www.sqlis.com/23.aspxThu, 03 Jul 2008 05:08:36 GMTkeymoohttp://www.sqlservercentral.com/Forums/Topic527853-148-1.aspxHelloI would like to know if SSIS is able to do the following: Have SSIS poll a folder and if a new Excel file has been copied there then import that file in SQL tables, the Excel files would not ahve the same name, but would have the same file structure.Thu, 03 Jul 2008 02:59:12 GMTolafdedig