Recently while working for a client that was running SQL Server 2008 R2 I was tasked with loading an Excel Macro (.xlsm) enabled workbook. If you have ever tried this yourself you may have tried using the default Excel provider, which does not work. In fact, this is the error message you get after trying to close the Excel connection manager.
I thought I’d go ahead and document the solution in both 2008 and 2012 for you. Both solutions require you have the Microsoft Access Database Engine 2010 Redistributable driver installed so make that your first step before reading on. Don’t worry it doesn’t require a server restart.
SSIS 2008
Create an OLE DB Connection Manager and use the Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider (Microsoft.ACE.OLEDB.12.0). Then provide the path for your macro enable workbook in the “Server or file name property”.
Next click on All to modify the Extended Properties by adding Excel 12.0 Macro;HDR=YES. Most of this text is self explanatory except the HDR which stands for header. If you don’t want the first row of data to be the column header than change this to NO.
Click OK and use an OLE DB Source in your data flow to either select a sheet or query the workbook.
SSIS 2012
Guess what? With SSIS 2012 you don’t have to do anything extra! The default Excel Connection that didn’t work in 2008 does work now. As long as you have the before mentioned Access driver you’re ready to go. Shown below is the 2012 Source Assistant that you’ll noticed took care of the Extended Properties setting you had to manually do in 2008. In 2012 you will use the normal Excel Source instead of the OLE DB Source.
Hope this helps!
Devin Knight 
This really helped me a lot..
Thank You
Awesome Post. It helped me import my xlsm file via SSIS 2008. Thanks a ton for sharing
Thanks a lot..
Can you please help me know if we have 64 bit production server this access component should also be 64 bit?
As i have 32 bit DEV server and also installed x86 version of this component and it worked.
I have only 1 question more, how can i use this or anything else to use xlsm files dynamically? so i have a folder full of xlsm files and want them to read one by one and execute each after load.
Load xlsm -> write to database -> use the table from database for data manipulate and write some data into other database -> truncate table -> read next xlsm file.
I can’ do this and i tried many hours, red many forums and nothing..
maybe u can help me
That was cool, but the data does not get exported to any other file..not even data viewer, can you please tell me why???Even though package is running in 32 bit mode…. 😦
I am using SSIS in VS 2008 and installed the Microsoft Access Database Engine 2010 Redistributable as you suggested. I use the OLE DB Source to connect to the Micro-enabled datasheet, I got this error:
SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered — perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.
Please advise how to fix it.
Thank you in advance.