Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«September 2015»
MonTueWedThuFriSatSun
311

Join Me for Free #PowerPivot Training w/ @ExcelBIPASS Sept 10 – 12p CDT

image

I’m excited to be able to say that coming up next week on Thursday September 10, I’ll be presenting my session Power Pivot 101: An Introduction to the PASS Excel BI Virtual Chapter! For a lot of users, Power Pivot is like the Ferrari you had in your garage but weren’t aware and that’s one of the reasons I’m so excited to be able to present on this topic. This session is completely free and available to all who would like to attend. It doesn’t get much better than that!

Register for free Power Pivot training

Power Pivot is a powerful yet flexible analytics tool built into a familiar environment yet many users remain unsure of how to take advantage of this dynamic tool. In this session, we’ll discuss the purpose of Power Pivot, where Power Pivot fits within your organization and the basics of designing a Power Pivot model that integrates disparate data sources with the goal of gaining previously unrecognized insight into key business metrics.

This free online training event is scheduled for Thursday September 10th at 12 pm CST/1 pm EST. If you’re interested in attending, all you need to do is RSVP here to let the organizers know you’re coming. It’s going to be a great event, a lot of fun and maybe even educational! ;)


Read more
23456
78910111213
14151617181920
21222324252627
2829301234
567891011

Using Excel Macro (.xlsm) Enabled Files in SSIS

  • 28 June 2012
  • Author: DevinKnight
  • Number of views: 12740
  • 0 Comments

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!

Print
Tags:
Rate this article:
No rating
DevinKnight

DevinKnightDevinKnight

Other posts by DevinKnight

Please login or register to post comments.