CODEWITHSUNDEEP
sql-serverssisoledbxlsx
Justin
Justin

Reputation: 23

Error on dynamic connection string for an SSIS OLEDB Connection

I’m having an issue building a dynamic connection string for an SSIS OLEDB Connection. The SSIS Package is designed to loop through xlsx files in a network folder. The package extracts the data from the files and loads it into 3 different SQL 2008R2 database tables.

This is the connection string I am attempting to use as an expression in the SSIS OLEDB Connection:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::Folder] + @[User::File] + ";Extended Properties=\"Excel 14.0 Xml;IMEX=1;HDR=YES;\";"

The error I’m getting is:

Error at Package [Connection manager "\test-0\finance\Test Expenses\New Test Files\Test.xlsx 1"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.

I’m hoping another set of eyes sees something simple I am just missing. It looks right to me!

EDIT: Here are some other connection strings I have tried:

Provider=Microsoft.ACE.OLEDB.12.0; Data Source= + @[User::File] + ";Extended Properties=\"Excel 12.0 Xml; IMEX=1; HDR=YES\";"
"Provider=Microsoft.ACE.OLEDB.12.0;"Data Source=" + @[User::Folder] + @[User::File] + "";"Extended Properties=\"Excel 12.0 Xml";"HDR=YES";"IMEX=1\""
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + @[User::File] + "; Extended Properties=\" Excel 12.0 Xml; HDR=YES\ ";"
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + @[User::File] + "; Extended Properties=\"Excel 14.0 Xml;IMEX=1;HDR=YES\";"
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + @[User::File] + ";Extended Properties="Excel 12.0 Xml;IMEX=1;HDR=YES";"
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + @[User::File] + ";Extended Properties="Excel 14.0 Xml;IMEX=1;HDR=YES;""
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + @[User::File] + ";Extended Properties=\\"Excel 12.0 Xml;IMEX=1;HDR=YES\\";"
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + @[User::Folder] + @[User::File] + ";Extended Properties=\"Excel 14.0 Xml;IMEX=1;HDR=YES;\";"
Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + @[User::Folder]+@[User::File] + ";Extended Properties=\"Excel 14.0 Xml;IMEX=1;HDR=YES;\";"
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::File] + ";Extended Properties=\"Excel 12.0;HDR=YES\";""
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::File] + "; Extended Properties=\"Excel 12.0 Xml; HDR=YES\";
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::File] + ";Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES;""
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::Folder] + @[User::File] + "; Extended Properties="Excel 12.0 Xml;HDR=YES";
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::Folder] + @[User::File] + ";Extended Properties=\"Excel 14.0 Xml;IMEX=1;HDR=YES;\";"

EDIT: If there is some other bit of information that would help please let me know. I'm willing to try anything. In fact, I'm in the process of rebuilding the entire project. I new to SSIS. I come from a systems background and I've got experience with development and SQL. Please forgive my poor formatting. I'm also just learning this site. Thanks.

Upvotes: 1

Views: 5661

Answers (1)

Gary Walker
Gary Walker

Reputation: 9134

For comparison, here is the string from connectionstrings.com

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

Which I have reformatted as below (ignore the embedded whitepage)

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
  c:\myFolder\myExcel2007file.xlsx;
  Extended Properties="Excel 12.0 Xml;HDR=YES";

When I reformat your string I get

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
    + @[User::Folder]+@[User::File] 
    + ";Extended Properties=\"Excel 14.0 Xml;IMEX=1;HDR=YES;\";"

Based on your error message

"\test-0\finance\Test Expenses\New Test Files\Test.xlsx 1"

It looks like the user::folder and usr::file worked as expected

I am not sure exactly how you are building your connection string, but are you sure the way you are embedding quotes marks on the last line is correct, i.e., should you be using '"' or """" to represent an embedded quote?

IIRC, IMEX=1 is generally a good idea on xls files

ADDED

OK, if you are building by, you should have showed the hard-coded string result. Based on all the parameter based stuff you showed, I thought you had to be building this in code somehow. Which means billinkc is likely correct in his comment re: the UNC path.

Upvotes: 1

Related Questions