Home Dashboard Directory Help
Search

Cannot use MDX queries in SSIS package with OLE DB Provider for Analysis Services 9.0 by Marco Russo


Status: 

Closed
 as Fixed Help for as Fixed


10
0
Sign in
to vote
Type: Bug
ID: 219068
Opened: 10/5/2006 3:06:57 PM
Access Restriction: Public
1
Workaround(s)
view
5
User(s) can reproduce this bug

Description

Any SSIS package with an OLE DB Source (with OLE DB Provider for Analysis Services 9.0) does not work if you try to put an MDX query as a "SQL command text": the preview works well, but when you run the package any query (even a very simple one) fails with a "Internal error: An unexpected error occurred (file 'pcrsstore.cpp', line 325, function 'PCRSStore::UnLock').".
Details
Sign in to post a comment.
Posted by Tetrahedron1 on 3/18/2009 at 6:51 AM
I am having the same issue. Data shows in Preview, and Columns show in Column View, but when I execute the task, It just sits there yellow. The Output Window shows "SSIS package "Package.dtsx" finished: Canceled." ANY Progress resolving this? Currently using SQL Server 2005 SP2.
Posted by Andrew Walaszek on 5/14/2008 at 1:32 PM
Is this bug fixed? Can I get patch for SQL Server 2005 with this fix?
Thanks,
Andrew
Posted by Pelucon on 3/12/2008 at 6:08 AM
I'm getting the same error: 0x80040E21 even after Cumulative Update 6 of SP2 (version 9.0.3228). The workararound of this problem, works really badly because it runs the full query 2 times (seen in profiler). It shows reading data from partition and aggregation 2 times, so it's not validating(metadata) and executing(data) as it should, but running 2 times. which decreases performance by 100 percent.
Posted by sujeetv on 1/17/2008 at 4:53 PM
I am still seeing this issue with SQl 2005 Sp2. Is the fix pasrt of Sp2 or a seperate download
Posted by Gennadiy Chornenkyy on 9/28/2007 at 7:46 AM
SQL 2005 Dev Ed with SP2 on Win XP sp2 - at least errors not "unexpected" now ... or any special hot fix available?

Error: 0xC0202009 at Data Flow Task, OLE DB Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.
Posted by Microsoft on 5/20/2007 at 7:30 PM
This problem has been already fixed. Thank you.
Posted by Microsoft on 10/9/2006 at 10:24 AM
Thank you for reporting this issue. We will investigate and get back to you.
Posted by Microsoft on 10/6/2006 at 11:53 AM
Thank you for reporting this issue. We will investigate and get back to you.
Sign in to post a workaround.
Posted by Marco Russo on 10/5/2006 at 3:11 PM
A possible workaround is to use a regular SQL Server OLE DB connection to a SQL Server 2005 database, using a query with the OPENROWSET statement. You have to enable the "Allow inprocess" flag into the MSOLAP Provider.
For example:

SELECT *
FROM OPENROWSET( 'MSOLAP','DATASOURCE=LUGANO; Initial Catalog=FoodMart 2000;',
'SELECT
{ Measures.[Store Cost] } ON COLUMNS,
NON EMPTY ( Product.[Product Family].MEMBERS * Gender.Gender.MEMBERS * Time.Month.MEMBERS ) ON ROWS
FROM Sales' )