PerformancePoint Planning: Bug in the bsp_DI_ValidateLabelTable Proc

Somewhat irrelevant due to shut down of PerformancePoint Planning but it's an issue I encountered so I will record it for posterity and future generations (*snigger*).

There is a bug in the bsp_DI_ValidateLabelTable proc which causes it to fail when you are using a specific hierarchy in the model rather than the default 'all members' set. The problem arises when you pass in an incorrect model name.

The proc does not validate that the model name is actually valid before continuing (naughty naughty) which means you can actually cause errors if it just so happens that the value you pass in is a valid object - just not the name of the model itself. Let me show you want I mean.

First let's just mock up some object labels (please note these are NOT real. I would never be so generic but for the purpose of examination...).

Application: PPS

RootModelSite: PPS_Planning

Model: PPS

When calling PPSCMD you often refer to PPS:PPS_Planning.

When executing the bsp_DI_ValidateLabelTable proc, it asks for modelname as a parameter. In my example, I will have a scope in FK_relationships for PPS:PPS_Planning to identify the modelsite but that's not the one I need. Nope. I need PPS_Planning:PPS, which identifies the model.

But I'm half asleep and I've typed PPS:PPS_Planning in the past 24 hours so many times that I just don't think. Uh oh, all of a sudden the proc fails - but not after having made a few updates already.

Silly mistake to make, no question. Still, it does seem a bit dodgy to take an string input from a user, which is also a unique label used to identify a specific record, and not validate that it actually exists before making updates using that value!

SSIS: Write to the Output Window from a Script Task

Want to write to the Output window during execution of a script task?

DTS.Events.FireInformation(1,"YOUR TITLE","Your Message","",0, False)

Really handy for debugging!

SSIS: Error when executing a data flow using the OLEDB SSAS 9.0 Provider

I found an interesting problem today.

I had an SSIS package which contained, amongst other things, a data flow which had 3 OLEDB data flow sources which later get merged into one for insert into the destination. Each data source points to the same OLEDB connection which uses the Analysis Services 9.0 provider to connect to a mining structure. Each data source used the SQL Command access mode and a simple DMX statement in the command text.

When previewing the results of each query all worked fine and results were as expected. When it came to execute time on the server I kept receiving the most frustratingly vague error.

"Error code = 0x80040E05, External Code = 0x00000000:."

Mmmm. Yes... I know it well...

So I presumed it was somehow a permissions issue as the previews under my profile all looked fine. Only, the perms were the same. I made sure the connection property "RetainSameConnection" was set to FALSE. No difference.

So then I deleted 2 of the data flow sources and tried again. This time success. Somehow the connections were locking each other out. I set the RetainSameConnection to TRUE. This time even the previews failed.

So my workaround? I used a ForEach Loop to do them one at a time using the "SQL Command From Variable" data access mode on the data flow source.

Problem solved!

SQL SSIS: Vote for data viewer suggestion

Vote for a feature request on Microsoft Connect for greater flexibility over when and where an SSIS data viewer is shown.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=465835

SQL: Retrieving the Metadata for a Query

Sometimes you need to know what the metadata for a query looks like before runtime. This is particularly relevant with SSIS.

I use it rarely but every time I do have a need for it, I can never remember the name of the option and it takes me an hour at least to track it down again. So I'm hereby cementing it in stone so I don't forget it again!

SET FMTONLY ON

That's the one. It will return the columns and data types to the query engine. Note: it does not actually run the query or return any results. It's purely to return metadata about the query.

It's surprisingly handy from time to time.

SharePoint Magazine - Part 3 Out Now!

My next article for SharePoint Magazine is available now.

http://sharepointmagazine.net/technical/administration/kpis-–-who-am-i-and-where-do-i-come-from-part-3-of-6

SQL 2005: Scalar Resultsets and SSIS Variables

Found an interesting SSIS tidbit today which I've amazingly never encountered before.  Not quite sure how I've never managed to hit this problem before - it seems surprising to me but there you go, I guess it happens from time to time.

In an ExecuteSQLTask, I was returning a scalar value to a variable.  The value returned was a bigint.  My SSIS variable was a bigint.   The step failed with an invalid type error.  What the?  Changed the type to an object.  Success this time but not much help to me down the control flow because I can't read it without a ForEach - which I really don't wanna do for a scalar value.  I mean that would just be silly.

According to Technet "With an OLE DB or Excel connection manager, the connection manager explicitly converts values of the following types, DBTYPE_NUMERIC, DBTYPE_GUID, and DBTYPE_BYTES, to strings".  Seems crazy to me!  I changed the SSIS variable to a string and what do you know, success.

Nuts!