Calling AMO code within a SSIS package

We execute our AMO code within SSIS packages by calling a custom written DLL (registered in c:\windows\assembly). Originally, I wanted to call the code via a SSAS stored procedure, however it did not handle transactions like I wanted them to.  Calling the AMO code within a SSAS stored procedure seemed to ignore transactions. This was a must since we need to rollback certain transactions in the case of a failure.
Here’s what the code looks like in a SSIS script task.
AMOCubeProcess is the name of the assembly registered in c:\windows\assembly, and ProcessMeasureGroup the name of a class within that assembly.

Imports System

Imports System.Data

Imports System.Math

Imports AMOCubeProcess.ProcessMeasureGroup

Imports Microsoft.SqlServer.Dts.Runtime


Public Class ScriptMain


    Dim AMOCubeProcess As New AMOCubeProcess.ProcessMeasureGroup


      Public Sub Main()


        Dim ASConn As String

        Dim ASDatabase As String

        Dim ASCube As String

        Dim ASMeasureGroup As String

        Dim SQLConn As String

        Dim EnviroType As String


        ASConn = "Data Source=CubeServer01;Provider=msolap;Initial Catalog=Sales"

        ASDatabase = "Sales"

        ASCube = "Sales Cube"

        ASMeasureGroup = "mgFactSalesRevenue"

        SQLConn = "Data Source=ETLServer01;Integrated Security=SSPI;Initial Catalog=DW;"

        EnviroType = "Production"


        AMOCubeProcess.ProcessAll(ASConn, ASDatabase, ASCube, ASMeasureGroup, SQLConn, EnviroType) 

        Dts.TaskResult = Dts.Results.Success

      End Sub


End Class

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s