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.

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.

Script task recompilation Error

A package that has a script task runs fine in visual studio, but the package fails as a job with following error on DEV Server.

"Script could not be recompiled or run: Attempted to read or write protected memory. This is often an indication that other memory is corrupt"

OR

“Error 1 Validation error. Script Task : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.”

Answer:

• Check the pre-complied property of script task.
• Change it to TRUE
• Add the following code in your script, before imports statement.

                    Option Strict Off

                    Option Explicit On
                    Imports System

                    .

                    .