Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot use MDX WITH MEMBER statement in ADO NET source


Cannot use MDX WITH MEMBER statement in ADO NET source

Author
Message
daifeng.hao
daifeng.hao
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 41
Hi, I tried the following MDX statement in ADO NET source but get a failure message

WITH MEMBER CountOfRows AS (
IIF(ISEMPTY(MEASURES.[Fact Test Result Count]), 0, MEASURES.[Fact Test Result Count])
)
SELECT CountOfRows ON 0
FROM [Fact Test Result]

The error messag is
Error: 0xC0209029 at Data Flow Task, ADO NET Source [87]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "ADO NET Source" (87)" failed because error code 0x80004002 occurred, and the error row disposition on "output column "[Measures].[CountOfRows]" (109)" 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.
Error: 0xC02090F5 at Data Flow Task, ADO NET Source [87]: The component "ADO NET Source" (87) was unable to process the data. Pipeline component has returned HRESULT error code 0xC0209029 from a method call.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "ADO NET Source" (87) 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.

However if I modify the above query to

SELECT MEASURES.[Fact Test Result Count]) ON 0
FROM [Fact Test Result]

I got no errors. Does that mean I can not use MDX WITH statement in ADO NET? Any walking around besides script task?
daifeng.hao
daifeng.hao
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 41
Noticed an interesting result. I am using Execute SQL Task Editor with Analysis Service 10.0 as ole db provider. I assigned the returning result to an INT32 variable. However when I debug the package, I got null as returning result, while when I was running the query directly in management studio, I got 0. Then I switched the variable type to INT16 and it give me the correct result (0). It seems the AS will assign data type dynamically based on the returning result. Does anyone has any experience in handling MDX in SSIS?
Mackers
Mackers
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 499
I have but I found the best way of doing this was to us a script component in the data flow The issue I found with having MDX in SSIS package is that when the MDX returns 0 rows it tends to cause the package to fail. This is because SSIS thinks it is returning less columns that it is expecting

Using a script component gets around this


Mack

Some sampe code


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.AnalysisServices.AdomdClient
Imports Microsoft.AnalysisServices
Imports System.Data.SqlClient
Imports AMO = Microsoft.AnalysisServices

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Private cn As AdomdConnection
Private cmd As AdomdCommand
Dim CubeReader As AdomdDataReader
Public FinalMDX As String


Public Overrides Sub PreExecute()
MyBase.PreExecute()

'Dim CubeReader As AdomdDataReader


Dim Cmd As New AdomdCommand
cn = New AdomdConnection(Me.Connections.SSAS.ConnectionString)
cn.Open()

Cmd = New AdomdCommand()
Cmd.CommandType = CommandType.Text
FinalMDX = Me.Variables.MDXHeader.ToString & Me.Variables.MDX.ToString
Cmd.CommandText = FinalMDX
Cmd.CommandTimeout = 0
Cmd.Connection = cn
CubeReader = Cmd.ExecuteReader()

End Sub

Public Overrides Sub CreateNewOutputRows()


Do While CubeReader.Read
With Output0Buffer

.AddRow()

.MyOutputColumn1 = CubeReader.GetValue(0)

End With

Loop

cn.Close()
CubeReader.Close()
End Sub
End Class


daifeng.hao
daifeng.hao
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 41
Thanks, Mack. Actually I tried another non-script way, which contains couple of data conversions in data flow. ADONET Source -> Copy the column assigning a simple column name (For certain reason I could not use Derived column DFT to access [Measures].[WhateverTheMeasure]) -> Data Conversion (Convert To DT_WSTR) -> Data Conversion ->(Convert To DT_I4 or Whatever the finial variable type is) ->Recordset Destination (pick up the DT_I4 column and assign to a object variable), and then use foreach container to assign to your target variable. This method is a little trivial but easy to debug on the fly.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search