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.
Environment:
SSIS package on SQL Server 2005. BIDS 2005
SSAS cube on SQL server 2008.
Solution:
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,http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
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)
connex.Open()
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
Else
writer.Write(dr(i).ToString() + " , ")
End If
Next
writer.WriteLine()
End While
End Using
dr.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
***************************************************************
Let me know your valuable comment to make this post better.
