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