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!

Speaking at PASS Summit 2012

Today I’m thrilled and humbled to announce that I will be speaking 3 times at PASS Summit in November 6-9!  This is my favorite of all conferences to attend and I hope you can make it too.  Go register now here.

A really fun way you can browse all the sessions that will be at PASS Summit is by using LobsterPot’s HTML 5 PivotViewer control http://pivot.lobsterpot.com.au/PASSSummit2012.

image

image

Here’s the sessions I’ll be delivering at Summit.

Creating a Power View Reporting Solution
Session Category: Regular Session (75 minutes)
Session Track: BI Information Delivery
Speaker(s): Devin Knight
Power View lets you create eye-popping visualizations within SharePoint 2010 and provides intuitive ad-hoc reporting that a variety of business users can use to make critical decisions. But before you can begin building these incredible reports, you must have your data properly prepared. This session will give you a beginning-to-end view of what is needed to create Power View reports, from organizing a Tabular model source to preparing the actual Power View report.

Adapting Your ETL Solutions to Use SSIS 2012
Session Category: Regular Session (75 minutes)
Session Track: BI Platform Architecture, Development & Administration
Speaker(s): Devin Knight
With all the great new features added to SSIS in SQL Server 2012, it is important to understand how they can make your development life easier. Join the discussion as we look at changes to usability, deployment, configuration, management, and more. This session will not only highlight what is new in SSIS but also show how your existing solutions can be adapted to utilize the latest features.

I’ll also be co-presenting with Brian Knight a full day preconference seminar with.

Building a SQL Server 2012 BI Platform
Session Category: Pre-Conference Session (full day)
Session Track: BI Platform Architecture, Development & Administration
Speaker(s): Brian Knight
In this day-long deep-dive seminar, you’ll learn how to build a Business Intelligence solution for your company and support it with the Microsoft platform. We’ll start with how to design and data model a data warehouse, including the system preparation. Then, we’ll jump into using SQL Server Integration Services to load the data warehouse.
After the data warehouse is loaded, you’re ready to roll up the data and provide slice and dice reporting with SQL Server Analysis Services. We’ll walk through cube development and enriching your cubes with added business metrics, which are essential for your future dashboards. Last, we’ll cover how to report against the data warehouse with SQL Server Reporting Services and SharePoint BI. Make sure you bring your own optional laptop so you can follow along as we design, load, and report against our BI solution.

Command Line Prompts for Checking Active Directory Membership

Using Active Directory groups are a great way to manage and maintain security for a solution.  Think about if you had to manually add users to your Analysis Services roles each time someone new wanted access to your cube.  Ideally, you would have an AD group in the SSAS role membership and anytime someone wants access to the cube they simply need to get added to the AD group and nothing would need to be changed in Analysis Services.

My goal in this post is not to convince you that AD groups are a good thing.  Hopefully you already understand that.  What I really want to show you is a few “tricks” for dealing checking permission to AD groups.

The first example will return back all AD groups that a user is a member of and lots of other good information about a selected user.  When troubleshooting access to your solution this gives you a quick way to rule out membership to the proper AD group as a possible issue.

Open a command line prompt by clicking your Start Menu and then select Run.  Type CMD and click OK where you will use the following code:

Template:

net user /domain “<AD Account>”

Example:

net user /domain “dknight”

image

The second example will return all users that are members of a specified AD group.  Open a command line prompt again and use the following code:

Template:

net group /domain “<AD Group>”

Example:

net group /domain “Schema Admins”

image

While these seem like simple commands you may find them very useful when troubleshooting permission errors.  Another great thing is you don’t have to be a server admin to use these just about anyone can run them.

New SSIS 2012 Expressions

SSIS 2012 has added a few new expression functions and I thought I’d spend a short time detailing each.

LEFT

The LEFT function believe it or not is a new function.  In past versions of SSIS you had a RIGHT function available but no LEFT.  Well SSIS expression haters you now have what you want.  This function is described as:

Returns the left part of a character expression with the specified number of characters.

Template:

LEFT( «character_expression», «number» )

Example:

LEFT( [ProductName],3)

Result:

Nik

TOKEN

The new TOKEN expression is an interesting one.  It returns a string after a specified Token delimiter.  You can pass in multiple delimiters for you expression to parse and also specify the occurrence number you would like to return.  That means if you set the occurrence to 3 it would return the third instance of the token.  SSIS describes this function as:

Returns the specified occurrence of a token in a string. A token may be marked by a delimiter in a specified set of delimiters. The function returns an empty string if the occurrence is not found. The string parameter must evaluate to a character expression, and the occurrence parameter must evaluate to an integer.

Template:

TOKEN( «character_expression», «delimiter_expression», «occurrence» )

Example:

TOKEN("new expressions can be fun"," ",2)

Result:

expressions

TOKENCOUNT

TOKENCOUNT would likely be used in combination with the previously discussed TOKEN function.  The TOKENCOUNT function returns back the number of times a Token delimiter appears in a string value.  This would likely be plugged into the TOKEN expressions for the number of occurrences when trying to find the last occurrence.  SSIS describes this function as:

Returns the number of tokens in a string. A token may be marked by a delimiter in a specified set of delimiters. The string parameter must evaluate to a character expression.

Template:

TOKENCOUNT( «character_expression», «delimiter_expression» )

Example:

TOKENCOUNT("new expressions can be fun"," ")

Result:

5

REPLACENULL

Again this function answers the SSIS haters who don’t like the fact that there is an ISNULL function in the expression language but it doesn’t work like the T-SQL ISNULL. Currently if you wanted to accomplish the T-SQL ISNULL you would have do write an expression like this:

ISNULL(OrderDateSK) ? 19000101 : OrderDateSK

This uses the ISNULL function that returns back True or False if the field is NULL and also uses a conditional operator to determine how to react when it is NULL.  The REPLACENULL function will work much more like the T-SQL ISNULL function.  This function is described as:

Returns the value of the second expression parameter if the first expression parameter is null.

Template:

REPLACENULL( «expression», «expression» )

Example:

REPLACENULL(  [OrderDateSK] , 19000101 )

Result:

19000101 (if OrderDateSK is evaluated as NULL)

Follow

Get every new post delivered to your Inbox.

Join 36 other followers