Hi Experts, I have Excel Connection Manager's Connection String as:- Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\ABC.xls;Extended Properties="EXCEL 8.0;HDR=NO"; But I want to make it dynamic by using my Package variable something like this:- Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\+@[User::FileName]+;Extended Properties="EXCEL 8.0;HDR=NO"; But it is giving me error, so to change Excel Connection Manager connection string to make it dynamic? Any Help Thanks Regards, Kumar

Set the expression for connection manager's connection string property as "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\TEMP\\" +@[User::filename]+ ";Extended Properties=\"EXCEL 8.0;HDR=NO\";"Nitesh Rai- Please mark the post as answered if it answers your question

Hello Kumar, Please check this out http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ab529613-fc43-4ab1-847f-098c082f3b7e/ Thanks

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"" + "EXCEL 8.0;HDR=NO" Declare a user variable and give entire path...this is working example. Let me know if this helped Thanks BB

Hi All, First I tried using Nitesh's reply, here is what I changed to the ConnectionString Property by right clicking on Excel Connection Manager and going into Properties:- "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\TEMP\\" + @[User::fileName] + ";Extended Properties=\"Excel 8.0;HDR=NO\";" Also I tried by making connection string using two different package level variable:- "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::folderName] + @[User::fileName] + ";Extended Properties=\"EXCEL 8.0;HDR=NO\";" Where, folderName value is C:\TEMP\ fileName value is going to for each loop and script component using below link, which is working perfectly:- http://www.bidn.com/blogs/MikeDavis/ssis/625/loop-through-excel-file-in-ssis I'm able to fetch all the excel file name by looping with the help of above link but with that I also wanna load the files into DB table and for that I need to make Excel Connection Manager dynamic. Any help where and what I doing mistake? Here is the error message I'm getting when I tried with both the solutions:- The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager. Thanks Regards, Kumar

Kumar, I think the you are getting Error at Path. why dont you take another variable (@Totalpath)and go to properties section, set evaluate expression to True and in Expression add following expression @[User::FolderName]+ @[User::FileName] and Use Totalpath variable in Connection Manager. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::TotalPath] + ";Extended Properties=\"EXCEL 8.0;HDR=NO\";" ***************************************************************************** if i understood correctly, you would like to read all excel files from a particular folder and load each and every file into Database and file source is an excel source. 1. Create a user variable with file path in it for example : @folderName="C:\Temp" 2.Drag a for each loop container, open it and configure enumeration properties such as Directory(@uservariable) and FileSpec("*.xls") 3. In variable mapping tab create a new user variable (@fileName) and assing value 0 to it 4.take another variable (@Totalpath)and go to properties section, set evaluate expression to True and in Expression add following expression @[User::FolderName]+ @[User::FileName] and Use Totalpath variable in Connection Manager. Then in Excel Connection Manager Properties use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::TotalPath] + ";Extended Properties=\"EXCEL 8.0;HDR=NO\";" In Excel Connection Manager Properties, Set DelayValidation Property to True Now the Package should run perfectly fine. Let me know if this helped

still the same error message, I did all the same steps as you have mentioned, but still having issues.......... even I tried re-creating package on BIDS 2008 but I have another issue, here is the BIDS Bug:- https://connect.microsoft.com/SQLServer/feedback/details/363373/ssis-foreach-loop-editor-loading-wrong-configuration-control-during-initialization?wa=wsignin1.0 Any Help? Thanks Kumar

whenever I'm trying to change the connection string to :- "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::TotalPath] + ";Extended Properties=\"EXCEL 8.0;HDR=NO\";" It gives me above same error message and also the connection string changes back to :- Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::TotalPath] + ";Extended Properties=HDR=YES;\"EXCEL 8.0;HDR=NO\"; I have no idea why it is changes by appending HDR=YES; Please Help? Kumar

Experts, I have created a sample example to represent my issue, I have uploaded my SSIS 2005 package, sql scripts, and two simple excel files, here are their links:- http://www.keepandshare.com/doc/2316488/excelfilenamedbtable-dtsx-october-20-2010-7-45-pm-56k?da=y http://www.keepandshare.com/doc/2316487/excelfilename-sql-october-20-2010-7-45-pm-1k?da=y http://www.keepandshare.com/doc/2316485/excel1-xls-october-20-2010-7-45-pm-17k?da=y http://www.keepandshare.com/doc/2316486/excel2-xls-october-20-2010-7-45-pm-17k?da=y Change the connection string and for the excel connection manager where I can load files dynamically into my ExcelFileName DB table and share back to me the package at deep.apex1393@gmail.com My expected o/p:- SELECT * FROM ExcelFileName ID NAME FILENAME 1 Kaumil D:\TEMP\Excel1.xls 2 Dhaval D:\TEMP\Excel1.xls 3 Chirag D:\TEMP\Excel2.xls 4 Jigar D:\TEMP\Excel2.xls Please let me know if you have any doubts or questions. Thanks Regards, Kumar

Is there any default value set for the variable TotalPath? Set a genuine value for this variable and use it in expression. Select the connection manager and go to expressions. Click on the ellipsis (...) against the expression property and select the connection string property. Set the expression as : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@[User::totalpath]+ ";Extended Properties=\"EXCEL 8.0;HDR=NO\";"Nitesh Rai- Please mark the post as answered if it answers your question

Hi Nitesh, I have three package level varibales:- First, folderName value D:\TEMP\ Second, fileName value 0 (which I'm fetching through for each loop container and will be inserting into 3rd column of my DB table.... doing same as "BeginnerBachi" has replied in his last response) Third, totalPath value expression as @[User::folderName] + @[User::fileName] (which makes as D:\TEMP\Excel1.xls or D:\TEMP\Excel2.xls) I tried your reply but still still getting error, this time it is different type of error message:- TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Data Flow Task [Excel Source [128]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. Error at Data Flow Task [Excel Source [128]]: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database. Error at Data Flow Task [DTS.Pipeline]: "component "Excel Source" (128)" failed validation and returned validation status "VS_ISBROKEN". Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ BUTTONS: OK ------------------------------ Here is what I'm doing on Excel Connection Manager, 1. right click on Excel Connection Manager 2. going to Expression 3. clicking on ellipsis (...) button 4 selecting ConnectionString property from the drop down list 5. again clicking on ellipsis (...) button to open Expression Builder 6. pasting below script under Expression: of the Expression Builder "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::totalPath] + ";Extended Properties=\"EXCEL 8.0;HDR=NO\";" 7. Hitting "Evaluate Expression" button, which gives me string as :- Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEMP\0;Extended Properties="EXCEL 8.0;HDR=NO"; 8. Hitting Ok on Expression Builder window to close it down. 9. Again hitting ok on Property Expression Editor to close it down. And guess what I'm getting red cross on the Excel Source on the data flow tab.!!!! :( Please let me know where I'm doing mistake? Thanks Regards, Kumar

How can @[User::totalpath] evaluate to D:\Temp\0 ?? Can you give a default value to variable totalpath? The default value should be : D:\Temp\file1.xlsNitesh Rai- Please mark the post as answered if it answers your question

hey if you got answer well and good...if not i think expression we are trying to use has errors..we are trying to use entire path in expression..since data flow is in for loop and the control is already in that particular folder, so full path is not necessary, replace totalpath with only file name. it shoud work fine created 2 variable foldername(C:\Temp\),filename( for loop mapping variable and value is 0 )and used following expression "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+@[User::filename]+ ";Extended Properties=\"EXCEL 8.0;HDR=NO\";" expression out put will be Provider=Microsoft.Jet.OLEDB.4.0;Data Source=0;Extended Properties="EXCEL 8.0;HDR=NO"; one more important thing is : Set Delay validation property on connectionmanager and package level to True Let me know BB