Connecting to an XLSX using SSIS

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)

01

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.

02

Once you have chosen the correct provider, type the full path of your XLSX file into the ‘Server or file name’ field.

03

‘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’.

04

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.

05

Configure the rest of your dataflow and now you will be able to read and/or write to and XLSX file.

06

Advertisements

Tags: , , ,

14 Responses to “Connecting to an XLSX using SSIS”

  1. G Clark Says:

    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”.

  2. dataintegrity Says:

    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.)

  3. G Clark Says:

    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.

  4. dataintegrity Says:

    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.

  5. Jon Says:

    This does not work in 64 bit machines because MS DB ACCESS 12.0 does not work in 64 bit.

  6. dataintegrity Says:

    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.

  7. SQL Server – Importando arquivos do Excel 2007 (xlsx) no SSIS | Tomás Vásquez - Blog Says:

    […] https://dataintegrity.wordpress.com/2009/10/16/xlsx/ // Share| […]

  8. Eyelet Curtains Says:

    i use both open office and microsoft office and i would say that microsoft office is more responsive and user friendly -;;

  9. Girish Jawale Says:

    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!!!

  10. namira Says:

    good job, its grateful..!!!!

  11. Jill Says:

    Thanks much, this is indeed working as stated in SSIS 2005 with an Excel 2007 .xlsx file.

  12. Geoff Says:

    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

  13. Slawomir Sidor Says:

    It works. Thanks!

  14. Sara Says:

    Thank you so so so so much for this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: