Learn how to a build a cloud-first strategyRegister Now

x
  • 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

Thanks.
0
patd1
Asked:
patd1
  • 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?
0
 
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\";"
0
 
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
0
 
Anthony PerkinsCommented:
I see so this has nothing to do with SQL Server.
0
 
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?

Thanks.
0

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