This method has worked beautifully for all my SSIS pkgs thus far. Basically, I use a Script Task to derive the name of the newest file in a local directory. Then I save the name of the file to user a user variable, e.g. User::File. Then, in my flat file properties > Expressions, I set "ConnectionString" to reference User::File. However, when attempting to use this method with an Excel source, I get this error message: Error atmyPkg [Connection manager "Excel Connection Manager"]: 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. Error at myPkg: The result of the expression "@[User::Folder]+ @[User::File]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. HELP......... I need this to work! Thanks

sadie519590 wrote: This method has worked beautifully for all my SSIS pkgs thus far. Basically, I use a Script Task to derive the name of the newest file in a local directory. Then I save the name of the file to user a user variable, e.g. User::File. Then, in my flat file properties > Expressions, I set "ConnectionString" to reference User::File. However, when attempting to use this method with an Excel source, I get this error message: Error atmyPkg [Connection manager "Excel Connection Manager"]: 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. Error at myPkg: The result of the expression "@[User::Folder]+ @[User::File]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. HELP......... I need this to work! Thanks The Excel connection string needs to be more than just the file name - it also needs to identify the sheet or range within the file from which the data will be extracted. Tip: Create a valid Excel connection string using the designer, and then copy and paste this string into notepad or another text editor. This will give you the format you need to follow. Then update your expression to build the exact same format, and you should be golden. If you need more help than this, please include the value to which your expression evaluates along with an example of what the connection string should look like for your example.

Sadie, if you take a look at a ConnectionString as provided when you first specify an Excel file, you'll see that it is much more than a file path. The ConnectionString for Excel looks like: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\PhilsExcelFile.xls;Extended Properties="Excel 8.0;HDR=NO"; So in your expression, you'd want to do: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::File] + ";Extended Properties=\"Excel 8.0;HDR=NO\";"

I'm sorry, I really don't see this anywhere! In my Excel connection manager, the path to my Excel file is simply: d:\myPath\myExcelFile.xls None of that other information is there. Where can I find this? I recreated it, and it was the same path.

Right click on the connection manager and select "Properties." Then, look at the value contained in the ConnectionString property.

Still having major problems :-( I found the ConnectionString property. So, I set my User::Folder variable to Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\myExcelFilePath\ User::File is empty, as this gets set in Script Task. Then in the Excel Connection Manager "expressions", I have ConnectionString set to @[User::Folder]+ @[User::File] Now, there's a big red X through my Excel File Source. Error is: Error1Validation error. Data Flow Task: Excel Source -myExcelFile [1122]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.myPkg.dtsx00

oops, never mind i messed up

help... Still not working: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::File] + ";Extended Properties="Excel 8.0;HDR=YES";" Gives me this error: Attempt to parse the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+@[User::Folder]+@[User::File]+";Extended Properties="Excel 8.0;HDR=YES";"" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

If you look closely at my example, you'll notice the escaping of the double quotes (\") surrounding the Extended Properties piece. Copy and paste what I wrote.

Yes, I realized that after the fact. I've updated it, got the expression to evaluate correctly. However, one thing that is different with the Excel connection vs. flat file connection manager, is that you MUST have dummy file name. In my case, I had to create a value for "User::File" under variables,or else I got major errors. With my flat files, I don't need this. However, during run-time, the "User::File" variable gets updated on the fly, so it doesn't use the value for the dummy file name. I know, because the dummy file is empty, and no data would get into the table otherwise. Can someone please confirm this "experience." I think it's kind ofless than idealI must maintain an actual dummy file at all times, or else my package will break. Thanks everyone so much for your help!!!

It needs a dummy file in order to validate the connection.

I've read the answers to Sadie's problem but they don't seem to apply to me. My Excel connections are to and from a File System Task. I am copying a template source spreadsheet to a destination spreadsheet, preparing it for the next 2 packages in my run. For a File System Task, the only property is the Excel file Path, which you can populate by browsing or direct input. There is no possibility of adding any expressions, and I can't make it accept variables in the path. I can't believe it's not possible to do this but I can't find the answer anywhere on the net. I've already posted the question on SQLServerCentral but no-one has answered. I'm getting a bit desperate ...

In the SSIS File System Task, for a copy operation, you should be able to choose to use a File connection or a variable as the source of the file path. Do you not have that option?

OK. I had a few problems with my client as I hadn't installed SP2. I've got a bit further now, but things still aren't working for me. I have an excel destination, and I set up the connection pointing to a real file and tested it, and it worked. The connection string showed: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\ITID\CUSTOMER SERVICE\Problem Management\Analysis team\Mirror\DEV\Automated Reports\Peregrine Reports\Output Reports\Time to Repair Analysis\DTS All Exceptions 2008225.xls; I then set up an expression for the connection string, as follows: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::XLOutput] This evaluates to: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\ITID\CUSTOMER SERVICE\Problem Management\Analysis team\Mirror\DEV\Automated Reports\Peregrine Reports\Output Reports\Time to Repair Analysis\DTS All Exceptions 2008225.xls The only difference is the missing ; from the end of the expression I set up, but this is put in to the connection string where it is shown above the expression in the properties window. When I try running this again I get the same error message as Sadie, together with further comments about it being an Unrecognized database format. Any ideas?

Few things, The Excel file must exist before you try to insert the rows in the data flow. You can create it via execute sql task in control flow before executing the dataflow Instead applying the expression against the connection string property; you may want to try applying ot against the ExcelFilePath one in the connection manager. I have done it that way before and it worked. See an example here: http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

Thanks for the reply Rafael. I had a look at your link, but my Excel file is more than just an output medium and I don't know how to do all the extra except in the way I am doing it. The first step of the task is a File System Task that uses the variables to copy a template spreadsheet to the one which receives the output in the second step. The template spreadsheet has a fixed name, but the path will vary depending on the environment. Itcontains 5 pages with headings, some pivot tables and some macros that allow the final recipient to run various tests against the data. This is copied to the output file with the variable name (run date appended). This also varies its path according to environment. I believe than in having this first step, my file will exist at the second step, but even if the package is being evaluated before it is run, the spreadsheet did exist when I tested it yesterday, because I had initially tested the first step alone. The copy statement allows for the destination to be overwritten On your second point, I had used the same variable to set the following properties: ConnectionString: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::XLOutput] ExcelFilePath: @[User::XLOutput] ServerName: @[User::XLOutput] Since these items all showed the path and name of my spreadsheet when I originally defined the connection, I made them all subject to the same variable, which will be provided at run time by the configuration file. Thanks again

I have read your all comments I also want to make my dextination folder variable according to date and thats why I am using expresiion in cexcel connection manager proprty as ExcelFilePath @[User::ExcelFilePath2]+( (DT_WSTR, 4) (YEAR (GETDATE()))+"_"+(DT_WSTR,2)(MONTH(GETDATE())) +"_"+ (DT_WSTR, 2)( DAY( GETDATE())))+@[User::FileNameAndExtenstion] But the problem is that I will create a job on this package and this package will have to create a new folder of having name containing date on which its running at that time it will throw an error. If you can help me too. Thank you in advance