Wednesday, May 5, 2010

Load CUBE data to SQL table using Script task

Recently I faced an issue while working with SSAS 2008 cube. I was using a data flow task and inside that I have OLEDB source (The Analysis Services 9.0 OLE DB Provider (msolap90.dll)) for fetching data using MDX query. I was running this package in BIDS, its working fine. But as I was trying to run it with DTEXEC command, it’s throwing following error:

• ERROR message:

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:.".

Component "OLE DB Source" (2297) failed the pre-execute phase and returned error code 0xC0202009.

SSIS package on SQL Server 2005. BIDS 2005
SSAS cube on SQL server 2008.

I have tried many possible solutions but no success. Finally I wrote a script task code which is connecting to CUBE and dumping the cube data in flat file with comma delimited format. Later on I used this flat file to laod data in SQL table.

I would like to share this code, may this helps other.

Important: Please add the "Microsoft.AnalysisServices.AdomdClient.dll" dll to your reference.

Microsoft.AnalysisServices.AdomdClient.dll is available under the link below,
Download "Microsoft ADOMD.NET " from the link above and after running msi file you will get
Microsoft.AnalysisServices.AdomdClient.dll under folder.
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.

Public Sub Main()
Dim serverName As String = "Manish"
Dim databaseName As String = "CRM"
Dim databaseID As String = databaseName
Dim cn As String = "Provider=MSOLAP;Data Source=" & serverName & ";Initial Catalog=" & databaseName
Dim connex As New AdomdConnection(cn)


Dim commandText As String

commandText = "SELECT [Country].[Country Code].MEMBERS ON 0, [Time].[Gregorian Calendar].[Year] ON 1 FROM [CUBE2008];"

Dim cmd As AdomdCommand = New AdomdCommand(commandText, connex)
Dim dr As AdomdDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

' output the rows in the DataReader
Using writer As StreamWriter = New StreamWriter("c:\myfile.txt")
While dr.Read()
For i As Integer = 0 To dr.FieldCount - 1
If (dr(i) Is Nothing) Then
writer.Write(" , ")
Continue For
writer.Write(dr(i).ToString() + " , ")
End If
End While
End Using

Dts.TaskResult = Dts.Results.Success

End Sub


Let me know your valuable comment to make this post better.