Hi Folks,

I am getting this issue while loading data from excel. The weird thing is that though it is showing this as an error but when I run my package still I am getting results and the task has turned to green.

Error  Message is displayed below. (Attaching the images too.)

TITLE: Microsoft Visual Studio
------------------------------

Nonfatal errors occurred while saving the package:
Error at Website [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 Website: The result of the expression "@[User::FilePath]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

 

You need a fix/patch (CU1 to SP 1) from http://support.microsoft.com/kb/2667037

ArthurZ, I cant do that. Its a production server and I don't know what kind of impact it'll have on other currently running packages.

Is there any option.

I am afraid if you presently do not have a patching process established then you will eventually have to have it.

You will not be able to overcome this nuance unless you apply that patch.

ArthurZ,

There are protocols which I have to follow for that. I know about this patch already, its that I cant do this.

I have already raised the request. BTW, thanks 4 your time.

On a separate note; by any chance do you have any idea, if I need to have a double quote symbol <"> in the string value, how should I write that in Expression Builder. Is there any escape sequence wildcard.

Sure, let them patch.

The escape shar is \

Thanks ArthurZ

Hi HimanshuSharma,

The issue may occur due to the improper connection string of the Excel Connection Manager. The expression for the ConnectionString property of the Excel Connection Manager should be like:

Provider=Microsoft.ACE.OLEDB.12.0; User ID=;Data Source= + @[User::FilePath] + ;Extended Properties="EXCEL 12.0;HDR=YES";

Alternatively, you can also set the ExcelFilePath property instead of the ConnectionString property for the Excel Connection Manager. In this way, the expression is much simpler.

Regards,

Mike Yin

Thanks for reply, I am marking yours as an answer though I solved it earlier. The problem was that my user file path variable contained a double quote <">.