Working with Analysis Services Objects using SSIS script task

In this post, we will see how to work with the Analysis Services Objects using SSIS script task. Follow these steps:

1. Copy the assembly Microsoft.AnalysisServices.Dll from c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder (or C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies) to the folder c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
   This is required to set reference to the required assembly with which we will later connect to Analysis Services Objects (AMO)
2. If you are using SQL Server 2005 SP2 and you get error for ‘script task can not find the binary code’, the resolution is at http://support.microsoft.com/kb/932557. You have to download a hotfix. You may have to restart your computer after you install the hotfix.
3. Add a connection to Analysis Services Connection and name it “ConnectionToCube”. Do not connect to any specific database.
4. Add a script task on the Control Flow tab and go to the design script (or Edit Script in SQL Server 2008) of the task
5. If you are using SQL Server 2008 you can create code in C# as well as VB.NET. The following code only lists all the databases with the help of the connection manager.

C#

public partial class ScriptMain :
    Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        ConnectionManager connMgr = null;
        Server server = null;
        string connStr;
        try
        {
            connMgr = Dts.Connections["ConnectionToCube"];
            server = new Server();

            connStr = Dts.Connections["ConnectionToCube"].ConnectionString;
            connMgr.AcquireConnection(null);
            MessageBox.Show("Connection obtained");
            server.Connect(connStr);
            MessageBox.Show("server connected");
        }
        catch (Exception ex)
        {
            MessageBox.Show("error occurred " + ex.Message);
        }

        foreach (Database db in server.Databases)
        {
            MessageBox.Show(db.Name);
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}

VB.NET

Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Public Sub Main()
        Dim connMgr As ConnectionManager = Nothing
        Dim server As Server = Nothing
        Dim connStr As String
        Try
            connMgr = Dts.Connections("ConnectionToCube")
            server = New Server()

            connStr = Dts.Connections("ConnectionToCube").ConnectionString
            connMgr.AcquireConnection(Nothing)
            MessageBox.Show("Connection obtained")
            server.Connect(connStr)
            MessageBox.Show("server connected")
        Catch ex As Exception
            MessageBox.Show("error occurred " & ex.Message)
        End Try

        For Each db As Database In server.Databases
            MessageBox.Show(db.Name)
        Next db
        Dts.TaskResult = CInt(Fix(ScriptResults.Success))
    End Sub
End Class