Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Using Excel Macro (.xlsm) Enabled Files in SSIS

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.

image

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

image

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.

image

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.

image

Hope this helps!

Comments

Leave a comment on the original post [www.bidn.com, opens in a new window]

Loading comments...