You have likely noticed that documents, templates, spreadsheets, and presentations that you create in the 2007 Office release are saved with new file-name extensions with an x at the end of the extension. For example, when you save a spreadsheet in Excel, the file now uses the .xlsx extension, instead of the .xls extension. In the 2007 Office release, Microsoft has adopted and XML-based file format that is said to improve file compression and provide better integration and interoperability of data. Unfortunately, SSIS cannot connect to an XLSX file using the Excel Connection Manager.
To use SSIS to connect to an XLSX file, we must use an OLE DB connection manager. First, drag and drop an OLEDB source in to your data flow view. (if you are exporting data to an XLSX you will want to use the OLEDB destination)
Double click on the OLE DB Source and create a new OLE DB connection manager. Select ‘New’. At the connection manager editor, choose the Microsoft Office 12.0 Access Database Engine OLE DB Provider.
Once you have chosen the correct provider, type the full path of your XLSX file into the ‘Server or file name’ field.
‘D:\XLSX\SAMPLE_SOURCE.XLSX’
Next, choose the ‘All’ button on the left side of the connection manager editor. Under the extended properties, enter excel 12.0 and then ‘Test Connection’.
After you have verified that you are able to successfully connect to the XLSX file click ‘Ok’. Choose the sheet that you wish to connect to (or the sheet you wish to write to, if you are using the XLSX as your destination) and click ok.
Configure the rest of your dataflow and now you will be able to read and/or write to and XLSX file.
Tags: Excel, Excel 2007, SSIS, XLSX
data integrity 
April 16, 2010 at 5:23 pm |
Is this SQL Server 2008? I’m using SQL Server 2005 and the Connection Manager dialog I’m seeing doesn’t look anything like your screen shots above. In SQL Server 2005 the OLE DB Source is expected to be SQL and doesn’t allow other connection types like “Native OLE DB”.
April 16, 2010 at 5:48 pm |
This is 2005 Integration Services. Odd that you do not see any additional connection strings. Out of curiosity, what version of SS 2005 are you running? (Enterprise, Developer, etc.)
April 16, 2010 at 6:00 pm |
This is all Enterprise. With Remote Desktop I’m logging into the server and running Visual Studio. When I click About Visual Studio I see this info:
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00
When I run SQL Server Management Studio and click About I see:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.3790.3959
Microsoft .NET Framework 2.0.50727.3607
Operating System 5.2.3790
I have a very conservative IT department that as a rule doesn’t install Service Packs for fear of introducing more problems than solving existing ones. As a result, I believe SQL Server 2005 hasn’t been updated on this server since it was installed from the CD in mid-2008.
Thanks for your help with this.
April 16, 2010 at 6:25 pm |
All of the posts in this blog have been implemented on SP 2 (9.2.3042.00). You are on the release version of SQL Server. I would recommend upgrading to SP 2 to take advantage of at least the security enahncements, the 150 + fixes and the native client enhancements. Depending on the complexity of your packages (and because of the conservative nature of your IT group), you may want to roll out SP 2 on a development server verify that there is no data degradation prior to implementing in production.
May 3, 2010 at 7:03 pm |
This does not work in 64 bit machines because MS DB ACCESS 12.0 does not work in 64 bit.
May 3, 2010 at 7:39 pm |
Yes, unfortunately Microsoft admittedly neglected that feature.
http://msdn.microsoft.com/en-us/library/cc280527(SQL.100).aspx
Time to run in 32-bit mode… It looks like BIDS 2008 Excel Connection Manager now supports the Excel 2007 file format.
August 25, 2010 at 11:19 pm |
[…] https://dataintegrity.wordpress.com/2009/10/16/xlsx/ // Share| […]
November 17, 2010 at 8:02 pm |
i use both open office and microsoft office and i would say that microsoft office is more responsive and user friendly -;;
April 15, 2011 at 11:42 am |
Wow this is really nice blog.
I wanted to do the same thing with file having extension “.xlsm”.
I did try this above procedure with file having extension “.xlsm”. It was able to preview data at design time but when the package is executed I am getting error.
Help would be very greatful!!!
December 6, 2011 at 3:21 am |
good job, its grateful..!!!!
January 12, 2012 at 8:26 pm |
Thanks much, this is indeed working as stated in SSIS 2005 with an Excel 2007 .xlsx file.
October 25, 2012 at 1:09 pm |
On Jon’s 64 bit issues,
you can get this to work by setting Run64bitRuntime to False in project properties and when the project is deployed it need to be run as 32 bit using CmdExec “C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe” /SQL “\FuelPriceDataImport” /SERVER “.” /USER [Username] /PASSWORD [password] /DECRYPT [Package password] /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING EW
June 3, 2014 at 2:15 pm |
It works. Thanks!
September 10, 2014 at 5:31 pm |
Thank you so so so so much for this.