CategoryBusiness Intelligence

SSIS 2012 Deadlock When Running SSIS from SQL Agent Job

I recently had an interesting situation where an SSIS package was scheduled to run at 1 AM but failed with a deadlock message. This seems a bit strange given that it was a deadlock on the step executing the SSIS package, not an error message from within the package. There was no history of a failure in the SSIS execution logs, just the job failure. As you can see, the error only happened a couple...

Remove Local Administrators SSAS Admin Access

Recently while changing some production security around on a new server I needed to modify the SQL Server Analysis Services (SSAS) administrator group at the server level. This is a pretty easy process; log in, add or remove admins as necessary in the security section. However, by default any user who is a member of the local administrator group on the server will also be an administrator on the...

Internal error: Invalid enumeration value. Please call customer support! is not a valid value for this element.

If you have done a lot of work with SQL Server Analysis Services (SSAS) you may have seen this message: We’ve all been there. A build error. I tend to get this because I like to forget commas when modifying calculations in the MDX section of the cube editor. It’s a fun little game I like to play to keep myself on my toes…question mark?? Anyway, what follows this message box is...

Color Expressions in SSAS Calculations

Color expressions in SSAS allow you to build an MDX expression to control the color of text displayed in a calculation. This property can be found in the Calculations tab of the cube editor when working in BIDS. Simply select a calcuation and look for the section labeled Color Expressions between the Display Folder and Font Expressions in the Additional Properties. Simply enter a condition in the...

PowerPivot for Excel 2013

The preview for Office 2013 is live!! You can hop over to the Microsoft website and grab it here: . Naturally I am eager to see all the BI features in both Office and SharePoint. If you have ever used PowerPivot then you know how cool the technology is. Well, with Office 2013 it gets even better. If you recall, PowerPivot WAS an add-on for Excel 2010 available for download at PowerPivot.com...

PowerPivot for Excel Now 2012 Only

I’m not exactly sure when this happened, but it looks like Microsoft has moved past the PowerPivot 2008R2 and PowerPivot 2012 model and is now just offering PowerPivot on their website. Previously there were two options as I just mentioned with the 2012 version being labeled RTM. Now that the 2013 previews of the office suite are out there it looks like Microsoft is officially pushing...

Understanding the CDC State Value

If you are using Change Data Capture (CDC) it can be very useful to look at the CDC State table to understand what is happening. I am writing this in the context of using SSIS 2012 CDC components; in this case the CDC Control Task. The CD State can be very helpful to determine why data isn’t getting loaded right after an initial load for instance, which is when I decided to dig into the...

SSIS 2012 Change Data Capture (CDC) Control Task

Change data capture was introduced in SQL Server 2008 and has been pretty popular ever since. The basic concept behind it is that all changes (inserts, updates, deletes) are captured and the details made available in a consumable manner. This means ultimately that all changes will be written to a table you can query to see everything that has been going on. In SSIS 2008 it was a little cumbersome...

SQL Server 2012 PowerPivot for Excel Prerequisites Error

PowerPivot was a great new tool when it released several years ago.  Now the newest version is out along side SQL Server 2012.You can still install the 2008 R2 version of PowerPivot or you can try out the new 2012 RTM version.  There are some nifty improvements that Devin Knight has chronicled (Blog | Twitter). There are several requirements that are not part of the 2008 R2 requirements.  The...

Trim Functions in SSIS

In T-SQL there are many functions there to help do a variety of different things.  SSIS is no different.  In fact, a good number of the commonly used functions from T-SQL are in SSIS as well.  Here we will explore the different Trim functions found in SSIS. We will be using the test phrase ”      Test      ” to illustrate the three functions: LTRIM, RTRIM and TRIM. LTRIM (Left Trim):...

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me