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

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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