MDX Queries in SSIS

Posted to SSIS forum but was told this forum might be of better help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=215075&SiteID=1

Essentially I'm using an OLE DB Source to fire off an MDX Query and I want to dump the results into a csv file.

I think I have this setup properly but when I run the package I get the following error.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x80004005 Description: "Internal error: An unexpected error occurred (file 'pcrsstore.cpp', line 325, function 'PCRSStore::UnLock').".

Can seem to find anything about this error, can anyone help?

Keith

Hi Keith.

First
Have you installed SP1 on you machine. If not, please try installing it and trying your experiment again.

Second.
Can you explain in a bit more details the way you've build your task in SSIS

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes I have installed SP1 on both the Ananlysis Server and my Client tools.

The tasks are pretty simple. I have 2 connections and 1 Data Flow Task.

Create OLEDB Connection, select
Native OLE DB\Microsoft OLE DB Provider for Analysis Services 9.0
as OLE DB provider, enter connection information.

In data flow, create OLE DB Source component, choose this connection, in Data access mode field select SQL command. Enter your MDX query in SQL command text box.

At this point I can preview the results of the query after receiving the following warning.

Warning at {C498A441-B272-48F7-AA8E-1C1BC11DDCD2} [OLE DB Source [1]]: The output "OLE DB Source Output" (11) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

I then see the results of the MDX query and the data is returned as expected.

I then have a Flat File Destination setup where I point the Data Flow Output to. It's a simple 2 column 1 row result set. I recieve no warnings or errors in the designer but when I run the package I get the error as explained above.

Just as a sanity check becuase this is my first time working in SSIS. I Changed the query to be a simple SQL query returning 2 rows and was able to run that just fine so I think I have the data flow setup properly.

In addition I have run a SQL Profile and I can see the query being sent to AS and the results being returned.

Appreciate the help.

|||

This looks like a bug with Analysis Services.

If you have some ideas try to work around it using SQL like syntax when sending query to Analysis services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.