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 2008 SP1 - a must have!

Some time ago I installed SQL 2008 on a VPC which was also running MOSS and PerformancePoint.  Soon enough the VPC started to run out of memory.  I tore my hair out for a few days because nothing I did made a good dent in the problem.  

Till I found the silver bullet.

Consistently SSAS was high on the list of memory-hungry services - plateauing around the 500Mb mark.  Yeah, that's a lot.  With only a couple of cubes and no queries running it did seem to be hugely excessive. 

What to do?  Install SP1.

That's it.

Usage dropped down to somewhere around 30-80Mb.  Much more memory friendly!

Interestingly, of the many bug fixes included, in SP1 (full list),  a memory leak in SSAS is not one of them! 

SQL 2005 SP3 has landed

SP3 for SQL 2005 is now available for download.

Included are a number of Bug Fixes , performance enhancements for using SSRS in SharePoint Integration mode, improved rendering of PDF fonts in SSRS, support for SQL 2008 Dbs with SQL 2005 Notification Services and support for Teradata Dbs as a data source for Report Models.

This Service Pack is cumulative and can be run on any version of SQL 2005.

PerformancePoint Planning: Error When Deleting Members of a Member Set

Unfortunately I discovered a problem in PPS Planning today which caused me a few headaches... thought I would pass it on to you!! The issue occurs when editing/creating a dimension member set... a save will fail if you do an outdent and a delete member immediately afterwards. Let me demonstrate.

Let's presume you have a structure something like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Salaries
--- --- --- --- Base Salary
--- --- --- --- Bonuses
--- --- --- --- Allowances
--- --- --- Tax Expense

But you want to remove the Salaries level and end up with a structure that looks like this:

--- Expenses
--- --- Salaries & Related
--- --- --- Base Salary
--- --- --- Bonuses
--- --- --- Allowances
--- --- Tax Expense

If you do this:

1. Outdent the 3 leaf level members
2. Remove the Salary level member
3. Save

You will get an error when you save similar to the following:

In order for it to work correctly, you MUST (in this order):

1. Outdent the 3 leaf level members
2. Save
3. Remove the Salary level member
4. Save
   

I have logged this problem on Microsoft Connect. Please feel free to vote! https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=375153&SiteID=181

BUG REPORT: VS Db Pro - TFS - Get Latest Version doesn't update error list

When using Visual Studio Database Professional (DataDude) and Team Foundation Server there are synching problems between team members when using "Get Latest Version" - the error list does not update to reflect changes. Allow me to give you an example:

1. DeveloperA deletes TableA and checks in. At this point, StoredProcA is in error because it depends on DatabaseObjectA - this is shown in DeveloperA's error list in Visual Studio.
2. DeveloperB does "Get Latest Version" on the project
3. DeveloperB corrects StoredProcA and checks in.
4. DeveloperA does "Get Latest Version" on the project

After the Get Latest, you would expect that DeveloperA's error list is now clear of the aforemetioned errors. Unfortunately it isn't. To get rid of these errors, Visual Studio must be restarted OR each error double-clicked. And every now and then, only a reboot will resolve it.

Have logged it with Microsoft Connect... you can view/watch it at https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=285730


******************************** UPDATE ********************************

This is the response from Microsoft.

Unfortunately, this is expected behavior. The database project doesn't know when all of the files have finished being updated, so it doesn't know when to refresh itself. When the project is in this situation, there is an information message in the info strip at the top of schema view telling you that you need to refresh. You can refresh the project one of these ways: . do a refresh on the solution explorer (button on the toolbar of solution explorer) . open the file that was modified . build . close/reopen the project