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


public partial class ScriptMain :
public void Main()
ConnectionManager connMgr = null;
Server server = null;
string connStr;
connMgr = Dts.Connections["ConnectionToCube"];
server = new Server();

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

foreach (Database db in server.Databases)
Dts.TaskResult = (int)ScriptResults.Success;
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Public Sub Main()
Dim connMgr As ConnectionManager = Nothing
server As Server = Nothing
connStr As String
connMgr = Dts.Connections("ConnectionToCube")
server = New Server()

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

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

No comments: