Thanks to this link: MSDN SSIS Team Blog
The Excel 2007 default output format for the SSIS Excel Destination is Excel Binary Format (.xlsb). If you’re creating a new Excel file, and have given it an .xlsx extension, you’ll get the following error when trying to open it in Excel:
Excel cannot open the file ‘xxx.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
If you want the Excel Destination to output a standard .xlsx file (Excel XML Format), you’ll need to tweak your Excel Connection Manager’s connection string property.
By default it will look something like this:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\xxx.xlsb;Extended Properties="Excel 12.0;HDR=YES";
Changing “Excel 12.0” to “Excel 12.0 Xml” will tell the provider to output in .xslx format instead.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\xxx.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES
Every once in a while you come across a problem that you wish someone else had faced before and posted the solution on the web. Just yesterday I found myself in that situation. So for anyone else out there that may encounter this same problem, here is my solution. My client has a Vision database for their Passport accounting system. They needed a way to pull data from their Vision database into their Microsoft SQL Server 2005 database (Express edition). The way I handled this was through the Acucorp extend AcuXDBC ODBC driver. The challenge was getting SQL to work correctly with the AcuXDBC ODBC driver. I first created a OLEDB for ODBC linked server inside of my SQL instance. Don't try this. The AcuXDBC driver doesn't like that. The only way I got it to work was by using the SQL command OpenRowSet(). I'll explain how this command works later, but first some additional gotchas:
I installed a new instance of SQL Express 2005 but did not reboot. I think this is why I kept getting the Vision ODBC error "GENESIS_HOME" environment variable not set - even though it was set. I noticed that that "GENESIS_HOME" environment variable was only set for the Administrator account and not system wide. So be sure to check for this and fix it before you do the reboot. In order to run OpenRowSet() commands you have to enable "Ad Hoc Distributed queries". Here is how to do it:
sp_configure ’show advanced options’, 1 reconfigure GO
sp_configure ‘Ad Hoc Distributed Queries’, 1 reconfigure GO
Now you can use the OpenRowSet() command. Here are the steps to do this:
Setup the AcuXDBC odbc driver as a System DSN. I named mine "PBS". Follow the AcuXDBC documentation for configuring the the ODBC driver. In my case it was already done for me. Here is how to use the OpenRowSet command. Note that the query syntax has to be SQL-92 compliant.
SELECT a.* from OPENROWSET('MSDASQL', 'DSN=PBS', 'select * from "mylibrary"."mytable"') as a
Good luck to you if you encounter this problem. Hopefully it won't take you 8 hours to get it working like it did for me.