Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1743
  • Last Modified:

ssis Unrecognized database format

Error "Unrecognized Database format 'C:\ChartDataExtracts\Excellus\EXCNY1PI10_Extract_09262011.xls'."

My excel destination in a data flow task works fine when I  connect it to a physical file (hardcoded connection string). But, I want to give the connection string in an expression for Excel Connection manager. So, I wrote the following expression for my Excel Connection manager and now I get the above mentioned error.

Expression: Connection String

Value:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::strDirectoryPath]+ "\\"+ @[User::strParentCo]+ "\\"+ @[User::strHealthPlanID]+"_extract_"+ @[User::strExtractDate]+".xls"+ ";Extended Properties=\"Excel 8.0;HDR=NO\";"

Evaluate expression gives the following value: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ChartDataExtracts\Excellus\EXCNY1PI10_extract_09262011.xls;Extended Properties="Excel 8.0;HDR=NO";

There is a physical file with the same name at: C:\ChartDataExtracts\Excellus\EXCNY1PI10_Extract_09262011.xls

Please help me set the expression for the connection string for Excel Connection manager

  • 2
  • 2
2 Solutions
Anthony PerkinsCommented:
Are you using SQL Server 64-bit?  If it is not 64-bit version of SQL Server, is JET installed on the server?
Alpesh PatelAssistant ConsultantCommented:
Please make sure which version of Office is installed.

If Office 2007 or above then use below connectionstring with your proper inputs.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::SourceFileNameCitrus]  + ";Extended Properties=\"Excel 12.0;HDR=YES\";"
patd1Author Commented:
I have MS Offoce Standard 2010. Pls see attached picture for Ms Office version.
I copied the prefix and post fix for the connection string from the excel connection manager that is pointing to a physical file and created my connection string accordingly, but it still does not work. On Name of the Excel sheet on my Excel destination editor I get "no tables or views can be loaded"

The excel connection manager that works (pointing to physical file) has connection string as follows:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ChartDataExtracts\Excellus\EXCNY1PI10_Extract_09262011.xls;Extended Properties="Excel 8.0;HDR=YES";

I also changed my expression to evaluate to the same. Why would it not work?
I also tried the connection string that you posed in your message, but still the same problem. I cant see sheet1$ for Name of the excel sheet on excel destination editor.

Thanks Ms Office about
Anthony PerkinsCommented:
I see so this has nothing to do with SQL Server.
patd1Author Commented:
My expression for the connection string is as follows:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ @[User::strExcelFileName]
+ ";Extended Properties=\"Excel 8.0;HDR=YES\";"

It evaluates to :
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ChartDataExtracts\EXL\EXCNY1PI10_H3351_Extract_09272011.xls;Extended Properties="Excel 8.0;HDR=YES";

And ! set Delay Validation property to true.

Now it works if a dummy file exists with that file name in that path, but fails if the file does not exist. In my scenario the file will not exist, it should create a new file, and that is the reason I am giving it an expression for the connection string. Each time the filename will be based on the project id and current date.

Any clues?


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now