Excel connection properities -

In my package I need to change the filename name for my input Excel based on a the contents of an external file. I want to read the external file, get the filename/location from this file and then dynamically set this as the property for the Excel Connection. I saw some posts related to this but did not understand which tasks I need to use and in which order.

thanks

My connection string is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

Delayed validation is True

I noticed my server name and excel file path are = to my source (=" + @.[User::ExcelFileName] + ") and if I change any of the 3 they all change.

When I run I get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Load training data [Read R013 Excel file [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Error at Load training data [DTS.Pipeline]: component "Read R013 Excel file" (1) failed validation and returned error code 0xC020801C.

Error at Load training data [DTS.Pipeline]: One or more component failed validation.

Error at Load training data: There were errors during task validation.

Error at PackageExcel [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Failure creating file.".

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK
-

thx

|||

Here is an expression that worked for me, exactly as entered in the Expression box. The double quotes around the Extended Properties value need to be doubled (and escaped). Note that in this case I was using a second user variable for the Extended Properties.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

-Doug

|||

Thanks for the response

When I paste in your code as is it gets changed automatically to this?:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

Doesn't like the (") in front of Provider? Any thoughts?

Tom

|||

Not sure how to explain that. But I did copy and paste exactly what appears in the Expression box in the Expression Builder dialog on my Excel Connection Manager's Expressions property...including the opening quote.

It's been a while since I ran that test when this issue first appeared in the forum, and I no longer recall all the variations that I tried.

The tricky quotes around the value of the Extended Properties argument are only required because there's more than 1 argument. If you just use "Excel 8.0" and allow the HDR argument to default to Yes, you can skip those quotes, which may well be your problem:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties=EXCEL 8.0;

-Doug

|||

I removed your variable for ext properties

have this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcellFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

still getting the error that it can't create file

Is there any way to view what's it's resolving the variable name to before it gets to the step of reading the excel file? I'm thinking that it is still not coming up w/ the correct file name. If I replace the variable w/ the hardcoded path/filename it works.

Thanks for your help

Tom

|||

I did get to see the name resolved and it looks fine

Tom

|||

I changed the variable to be the Provider instead of the Data source - this now gives me an error 'Class not Registered' - It seems like the implementation of the variable name has a syntax problem that formats it incorrectly.

Any suggestions?

Provider=" + @.[User::MyFileName] + ";

thx

|||

I got a bit lost in the thread above, but as an expression your example will not work. There is no opending quote and the semi-colon literal section is not quoted close. Maybe this forum mucks up the format but this should work-

"Provider=" + @.[User::MyFileName] + ";"

|||

Darren

when I tried to wrap the whole string w/ " the UI didn't like it and put a second 'Provider' at the end.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

In the above example I'm trying to set the data source w/ the parameter (what I really want) - when I couldn't get that to work I tried to use the parameter w/ Provider to see what would happen there.

Just to clarify what I'm doing (since I'm new to this), I'm setting the connectionstring property of my Excel connection manager

let me know if you have any other suggestions

thanks

|||

Just to be clear, you're entering this expression in the Expression Builder dialog box, right?

And in the Properties window for your Excel Connection Manager, the ConnectionString property itself is either (1) totally blank or (2) contains a complete, hard-coded, valid Jet/Excel connection string WITHOUT any of the expression syntax?

In other words, you're not entering the expression in the ConnectionString property directly?

|||

No I'm not - I had a feeling that I was doing something stupid - first time at using SSIS - I will give it a try - sorry for the confusion

Thx