Saturday, June 16, 2012

Excel File Connection Manager Connection String Problem

Today I encountered a following error on Sql Server 2012 while creating a basic SSIS package to load multiple Excel Files in database.

[Connection manager "Excel Connection Manager"] Error: 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.

I spent a couples of minutes to identify issue ,finally found solution:

Choose "Excel File Path" instead of "ConnectionString"while setting expression on Excel Connection Manager .


Hope this full help others who are facing the same problem.

Thanks,
Randhir

9 comments:

Julian Castiblanco said...

Hi Gyan,
it was very useful for me. I really aren't using excel connection in my package, but other connection in the project has the problem. How i don't using these connection in my package, it really was executed succesfully but in the ssisdb execution it failed by the connection validation. So, here is other kind of error that has relation with yours.

Note that it don't specified the connection of excel that has the problem

Error 1 Error saving DatosOutRXXXX.dtsx: 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. Z:\DatosOutRXXXX.dtsx 1 1

Unknown said...

Spent half a day trying to configure the excel connection string to work when deploying to SQL Server. Worked fine in debugging mode.

This has saved me much frustration from attempting to change characters within the connection string to get it to work.

Thank you!

Erick said...

Thank you very much!, I found a lot of pages but no one told me how to resolve this problem.

Hans Hoppe (DK) said...

Great:-)

Spent 2 days on the internet to find out what X=Y Means. I got a problem with an Excel file and connection manager configurations.

This here solved the problem:-)

Unknown said...

I just ran into this problem and found this post. Very helpful and saved me lots of time!!!

Thank you!!

Anonymous said...

as above, hunted the internet for a answer to the X=Y issue on the connection string. changed to this and worked brilliantly. Why cant MS have this fix on their site as opposed to sending you down a series of rabbit holes that are no longer relevant.
thank you so much

Anonymous said...

Why does Microsoft have to be so cruel and make everything harder?
I just think some things are make harder than they need to be. Thank you for the blog information.

Unknown said...

Thanks :)

Anonymous said...

Thank you for the quick tip. It was very helpful.