Thank you so much! (Aamir Shahzad Warraich and Kunal Joshi) I hadn't seen the Expressionbuilder - so I was just pasting in my expression in the properties windows (and even tried manually adding the code). And Kunal - I was missing the outer quotes... I had been wondering about those bust since Visual Studio was complaining about incorrect syntaxt whenever I added them manually into the connection-string I discarded them again... Thank you, it is now working :)

Hi there I am having some trouble using dynamic filename in my SSIS package to open an excel document. I am very new to this, so please have that in mind when instructing me. I have found other threads with similar problems, but the solutions I found does not work in my case: I have a package with a Data Flow Task. This data-flow task has set DelayValidation to true. In this Data-flow I have an excel source that is connected to a row-count. Very simple. It works perfectly when my Excel connection is set to a static .xls file: Then my Excel connectionstring is like this: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Projects\DNSL\test.xls;Extended Properties="Excel 8.0;HDR=YES"; But when I try and add a variable (Score=Package), it simply fails miserably. The variable I added is a String called "FileName" and it's value is the filepath: "D:\Projects\DNSL\test.xls" I then change the connectionstring to this: *1) Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=HDR=YES;\"EXCEL 8.0;HDR=YES\"; Note: I do try to add the following connectionstring: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\"; But for some reason, Visual Studio changes this connectionstring to the *1) one automatically?! This might be where the problem is? I have used the sample at http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html for reference, and that guy doesn't have this problem :/. The error I am getting is this: =================================== 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: "Could not find installable ISAM.". 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. (Microsoft Visual Studio) =================================== Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction) at Microsoft.DataTransformationServices.Design.PipelineUtils.AcquireConnections(IDTSComponentMetaData90 componentMetadata, Connections connections, IServiceProvider serviceProvider) at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.AcquireConnections() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args) Is there any1 who has an idea to solve this issue? Thanks Michael

Can't you just set the expression on the property ExcelFilePath instead of on the ConnectionString? Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Hello, How you are building your connection string? Did you go to connection Manager properties and then expression and then selected Connection String in properities and wrote expression that you have written with your FileName variable? If i have to use the different filename , i don't build the connection string, instead of that I go to Connection Manger, properties and then Expression and select ExcelFilePath from Property Drop down and in Expression Editor use FileName Variable with computer path ( C:\myfolder\myfile.xls) Set Delay validation true on Connection Manger as well on Data Flow. See if it will work for you. Thanks Aamir Shahzadhttp://sqlage.blogspot.com/

Michael seems like u are missing opening and closing quotes ... "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";" Plz copy paste as is {with quotes } as the connection string in expresion builder for connection string of excel onn manager Instead of Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\"; Hope that helps ... Kunal