Solved

ssis Unrecognized database format

Posted on 2011-09-26
5
1,374 Views
Last Modified: 2013-11-10
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
Comment
Question by:patd1
  • 2
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 36666505
Are you using SQL Server 64-bit?  If it is not 64-bit version of SQL Server, is JET installed on the server?
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 250 total points
ID: 36707588
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
 

Author Comment

by:patd1
ID: 36709663
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36712053
I see so this has nothing to do with SQL Server.
0
 

Author Comment

by:patd1
ID: 36712167
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now