SSIS – Connecting to Access 97 or Excel 2010

You would like to include data from Access 97 (because lets face it, they are still around) and/or Excel 2010 in your SSIS package.  Both methods are supported by installing the Microsoft Access Database Engine redistributable.   This driver needs to be installed on your SQL Server and facilitates the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 down to 97 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server.

To install, follow the download link and select either the 64 or 32 bit depending on your server operating system.

Note, before installing, make sure you don’t install both versions on the same server otherwise you may run into this issue.

Once installed, add a linked server to your Access database then attempt a SELECT from this database using OPENQUERY as follows.

EXEC sp_addlinkedserver
@srvproduct='OLE DB Provider for ACE',
SELECT * FROM OPENQUERY(access97, 'select * from table')

The following config may be required if you get an error message after attempting to SELECT above.

EXEC master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

Use a similar method to connect to Excel 2010.  Add the linked server then you can faciliate data transfer using OPENQUERY.

EXEC sp_addlinkedserver
@server = 'excel2010',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'C:\Temp\ExcelWorkbook.xlsx',
@provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

SELECT * FROM OPENQUERY(excel2010, 'SELECT * FROM [Sheet1$]')

However for Excel,  I had more luck using this workaround to generate the package using the 64 bit Import Export Wizard using the driver installed above but requiring an extended properties hack

Happy extracting!

About AussieBICG

Connect with me here

Leave a Reply

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

You are commenting using your 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