Solved

ssis Unrecognized database format

Posted on 2011-09-26
5
1,424 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server query 6 10
Merge two rows in SQL 4 17
Job - date manual 1 22
average of calculation (TSQL) 4 9
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
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…

832 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