Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Monday, October 28, 2013 2:15 PM
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.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
All replies (8)
Thursday, October 31, 2013 4:58 AM ✅Answered | 2 votes
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,
Monday, October 28, 2013 2:24 PM
You need a fix/patch (CU1 to SP 1) from http://support.microsoft.com/kb/2667037
Arthur My Blog
Monday, October 28, 2013 2:35 PM
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.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Monday, October 28, 2013 2:46 PM
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.
Arthur My Blog
Monday, October 28, 2013 2:50 PM
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.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Monday, October 28, 2013 3:03 PM
Sure, let them patch.
The escape shar is \
Arthur My Blog
Tuesday, October 29, 2013 1:49 PM
Thanks ArthurZ
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Friday, November 1, 2013 6:31 AM
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 <">.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.