Home Dashboard Directory Help
Search

Error using OleDB or DataReader to Get Analysis Services Data by mccpres


Status: 

Closed
 as Fixed Help for as Fixed


3
0
Sign in
to vote
Type: Bug
ID: 250920
Opened: 1/9/2007 2:13:56 PM
Access Restriction: Public
0
Workaround(s)
view
3
User(s) can reproduce this bug

Description

I've tried two methods to get data from an Analysis Services cube using MDX into my SSIS flow. One never works and the other only works as long as the query returns data.

I first tried the Ole DB source. When using this source, the source fails with an internal 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').".

I then tried a DataReader source. This method actually works as long as the query has data. If the query does not return data, then I get the following error:

Error: 0xC0047062 at Bad Performers, Bad Performers [1]: System.NullReferenceException: Object reference not set to an instance of an object.
at System.Data.OleDb.OleDbDataReader.GetRowHandles()
at System.Data.OleDb.OleDbDataReader.ReadRowset()
at System.Data.OleDb.OleDbDataReader.Read()
at System.Data.OleDb.OleDbDataReader.NextResult()
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

So, my current workaround was to increase the maximum error threshold on the package. I execute this query in a loop, and for the queries with data work fine...but if no rows are returned, then it errors. I don't see any easy way to trap this error and just have to ignore errors at the package level.

Any ideas on a fix? I'm runing 2005 with SP1.

Thanks, L
Details
Sign in to post a comment.
Posted by GSP7588 on 1/31/2014 at 8:58 AM
I faced similar problem in SQL Server 2012 while pulling data from SSAS cube using mdx query. I'm using OLE DB connection provider for connecting to cube and used 'Format= Tabular' in the connection string as well. Following error occurred in the pre execute phase of the Data Flow Task.
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server 2012 Analysis Services." Hresult: 0x80004005 Description: "Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')."

Is it something related to version of SQL Server? Is there any work around for the same?
Posted by snirdyce on 8/2/2012 at 6:33 AM
I have this problem too with OLE BD Provider for Analysis Services 2008 with the same scenario "for each loop which pulls the data from the cube by using mdx query, it is working fine for the some of the loops(iterations), after some iterations it is failing with the following error" :
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2008." Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.". End Error Error: 2012-08-02 16:15:12.58     Code: 0xC004701A
Posted by Pavan Amirishetti on 5/1/2012 at 10:45 PM
I have same problem with an OLE DB Source using the 'OLE DB Provider for Analysis Services 11.0'

I have a for each loop which pulls the data from the cube by using mdx query, it is working fine for the some of the loops(iterations), after some iterations it is failing with the following error

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server 2012 Analysis Services." Hresult: 0x80004005 Description: "Server: The operation has been cancelled.".

OLE DB Source failed the pre-execute phase and returned error code 0xC0202009


If i use 'OLE DB Provider for Analysis Services 10.0' got the following error

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2008 R2." Hresult: 0x80004005 Description: "Internal error: An unexpected exception occurred.".
Posted by Santosh Kumar Joshi on 12/4/2011 at 10:06 PM
Hi,

In my project, I am fetching data from SSAS 2008 to flat files using SSIS 2005. I am using MDX in OLEDB source and using Analysis Services 10.0 Native OLEDB driver. The MDX which is used for first time job run takes around 5-10 minutes but on the subsequent loads another MDX is used for daily data processing which takes 30-60 seconds.

Having said this, I am facing a weird issue i.e. instablity in the connection and job failing and throwing below error -

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".

When I re run the job, it runs successfully again. I am not able to figure out what is the issue? Is this a known limitation of connectvity between SSIS 2005 and SSSA 2008?

Any help/work around here would be really appreciating.
Posted by covasjc on 10/14/2010 at 12:02 PM
Thanks a lot Dipanjan... you're already save me a lot of time.
Regards,
Julio Cesar
Posted by Dipan1 on 3/28/2008 at 8:53 AM
I have got the fix for the Analysis Services OLEDB Provider. I created a sample application with AdventureWorksDW which runs a simple MDX query and moves the data to a text file.
Initially I also got the following error:
[OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80004005. 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').".
[DTS.Pipeline] Error: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

To fix this problem we need to follow the steps below:
·    Open the OLEDB Connection Manager
·    Add ;Format=Tabular after the Server name
·    Doing this will add it to the Connection String property.
·    This is the sample connection string I have after I added the property:

Data Source=localhost;Initial Catalog=AdventureWorksDW;Provider=MSOLAP.3;Format=Tabular;Integrated Security=SSPI;

Once we do this it works just fine. The only thing needed for this to work is for SQL Server to be on SP2 which I guess you are already on.
Please let me know if this works for you.

Thanks and Regards,
Dipanjan.

Posted by Norse on 2/12/2007 at 9:42 PM
I have same problem with an OLE DB Source using the 'OLE DB Provider for Analysis Services 9.0'. The behaviour changed slightly with SQL Server Service Pack 2 CTP (SQLServer2005SP2-KB921896-x64-ENU.exe). The error now appears as:

[OLE DB Source [3446]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
[DTS.Pipeline] Error: component "OLE DB Source" (3446) failed the pre-execute phase and returned error code 0xC0202009.
Posted by Microsoft on 1/9/2007 at 4:03 PM
Thanks for reporting this issue -- it should already be fixed in SP2. In fact, you may even be able to confirm this by downloading SP2 CTP3.

Thanks!
Sign in to post a workaround.