Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

SSIS 2008 ADO NET Source issue with SSAS data source – error code 0×80004002

Posted by denglishbi on March 29, 2009

A few weeks back I upgraded a SSIS 2005 package to SSIS 2008 and ran into a issue with my Data Flow Task source.  In the original package I was using a DataReader source to query information from Analysis Services (SSAS) and during the upgrade this gets converted to a ADO NET source in SSIS 2008.  The reason I was using a DataReader with an ADO.NET connection manager was because you could get around the annoying about being unable to map external data properly and that it will use the DT_WSTR data type when you try to preview the results of your query for the data source.

OLE DB SSAS Warning

I did a blog posting last year Using Reporting Services (SSRS) with SSAS data where I went through some different options of getting SSAS data into a format to easily use with SSRS.  This is when I basically decided that I would use the DataReader over the OLE DB source when using SSAS.  Also, if you use the OLE DB data source and have the Data Flow Task embedded in a Loop container this could cause some issues depending on the connection manager setting (more on this later).

So back to the ADO NET source issue… after I did my upgrade and ran my package I noticed a couple of things now with the ADO NET source.  First you get a warning message now in regards to the data type that I did not get with the DataReader source in SSIS 2005:

Warning    3    Validation warning. ADO Source: {568FF45A-6A41-43F8-A122-4BBFF40DD7BF}: The data type "System.Object" found on column "[Measures].[Reseller Sales Amount]" is not supported for the component "ADO NET Source" (1). This column will be converted to  DT_NTEXT.      Package2.dtsx    0    0   

And then now when the package is executed the ADO NET source does not work pulling to extract the SSAS data in the Data Flow Task even though you can Preview the data just fine:

image 

image

And the Error messages that I got were not what I would consider helpful in resolving the issue:

[ADO NET Source [43]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "ADO NET Source" (43)" failed because error code 0x80004002 occurred, and the error row disposition on "output column "[Measures].[Reseller Sales Amount]" (67)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.


[ADO NET Source [43]] Error: The component "ADO NET Source" (43) was unable to process the data. Pipeline component has returned HRESULT error code 0xC0209029 from a method call.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "ADO NET Source" (43) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I was able to find a KB Article that talks about a similar issue when using Oracle (http://support.microsoft.com/kb/959794), so I thought I just needed to install the SQL Server CU2 or higher.  I went with the SQL Server 2008 CTP SP1 just to make sure I was using the latest and greatest and unfortunately this did not resolve my issue.  So what do I do now… post the issue on Microsoft ConnectNerd  I went ahead and logged the bug on Connect here – SSIS 2008 ADO NET Source issue with SSAS data source – error code 0x80004002 and have already gone back and forth a couple of times with Microsoft about the issue.  You might sense some of my frustration in the bug posting.  I did decide to move on with the development and switch the data source over to an OLE DB source.  This brings up what I consider to be a bug with that data source is that when the Data Flow Task is embedded in a Foreach Loop container you need to make sure that you put a Script Task after (or this could be before I guess) the Data Flow Task that puts the thread to sleep prior to performing the next loop.  If you don’t you will get the following error:

[SSAS OLEDB Cube Source [928]] 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 2008."  Hresult: 0x00000001  Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".

[SSIS.Pipeline] Error: component "SSAS OLEDB Cube Source" (928) failed the pre-execute phase and returned error code 0xC0202009.

So what do you need to get around this issue?  You need to make sure that the ‘RetainSameConnection’ property on the OLE DB connection manager is set to ‘False’.  I also had some initial issues after changing that property value, so I also added a Script Task to the Control Flow within the Foreach Loop Container after the Data Flow Task with a single line of code that puts the thread to sleep (yes I am a fan of Visual Basic and the code for C# is not different for this except you need to include semicolons):

    Public Sub Main()

        System.Threading.Thread.Sleep(15000)
        Dts.TaskResult = ScriptResults.Success

    End Sub

Once you have verified the connection manager property and have this added to the Control Flow the package will run fine.  Still get the annoying warning messages when trying to preview and work with the OLE DB source, but at least the package runs.

Here is the final layout of my SSIS 2008 package:

image

If you would like this issue with the ADO NET source resolved so that you can use this with SSAS then please vote for this bug here – SSIS 2008 ADO NET Source issue with SSAS data source – error code 0x80004002Thumbs-up

Also, if you want to setup the SQL command for the ADO NET source so that it is dynamic you can do this within the Control Flow by using an expression on the Data Flow Task for the ADO NET source SqlCommand property (same thing for SSIS 2005 and the DataReader source).  It would be nice if you could just reference a variable within the ADO NET data source editor like the OLE DB source, but oh wellThinking

Advertisements

7 Responses to “SSIS 2008 ADO NET Source issue with SSAS data source – error code 0×80004002”

  1. Unknown said

    Good article but the last paragraph about the ADO NET source SqlCommand property was was icing on the cake for me. I have been looking all over for that info. Thank again.

  2. Dan said

    I agree, they could have made this setup just like the OLE DB source. That would have been nice. Sometimes you just have to step back a bit and in this case a tab to see what the other possibilites and options are. That is one reason I like SSIS so much is that it is so flexible and you can quickly find alternatives and implement them to meet your needs.

  3. Mackers said

    The way I got around this was by using a script transformation as a source. This way you get around issues with SSIS validating what you are doing (and subsequently erroring) and you don’t need to add in the wait step.
    I’ve been having issues wuith SSIS validating Sybase data and again the script transformation comes in handy

    Code is here – https://connect.microsoft.com/SQLServer/feedback/details/424791/ssis-2008-ado-net-source-issue-with-ssas-data-source-error-code-0x80004002

  4. Hi Dan,

    I am facing similar kind of issue as mentioned in your blog. I am trying to fetch SSAS 2008 data using SSIS 2005 SP2 & OLEDB as a connector but I am getting following error intermittently –

    [SSAS OLEDB Cube Source [928]] 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 2008.” Hresult: 0×00000001 Description: “Error Code = 0x80040E05, External Code = 0×00000000:.”.

    RetainSameConnection property is set to false already. In MSDN, Arthur recommended to use SP4 but before going that I want to see other options/opinions – http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/99229e33-2fb7-43da-84b2-dd54daf04d05.

    Regards,
    Santosh

    • denglishbi said

      Do you have a loop setup in your task as well or is the DFT just being called once? If you do have a loop where this is being called repeatedly or maybe back-to-back DFT in the control flow then I would try adding a pause (wait script) inbetween each one to see if you get better consistency that way and try running the package multiple times to see if this resolves your issue.

      • Hi Dan,

        No, there isn’t any loop in my package, however, it basically has 2 conditional branches among which only 1 executes at a time. In each branch there are 2 DFTs. One of the DFT has MDX which takes more than 5 minutes to execute and rest has MDX whose execution time is less than a minute. I also noticed that my package takes double the time of total MDX execution time, due to validation, is there any way to avoid this validation each time and reduce the time by half?

        As per your suggestion, I probably would add a delay script in between the 2 DFTs or may be get rid of the second DFT to check if it works.

        Thanks for answering.

        Kind regards,
        Santosh

  5. akabelle said

    Hello!

    I would like to ask you a question: I’m working on my thesis about Data Warehousing. I’ve built up a warehouse and also OLAP cubes using Business Intelligence Development Studio. Now I must make a C# tool for querying the cubes, and I read about ADO NET, but I couldn’t find any reference about it’s usage with Microsoft SQL Server 2008. Can you tell me what .dll do I have to use? Is it the same as in the case of MsSQL Server 2005?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: