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!