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.
patd1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
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 PatelConnect With a Mentor Assistant 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
All Courses

From novice to tech pro — start learning today.