SSIS – Inferred Member Insert in Fact Table Load

Recently while working on a different solution for loading inferred member solution in our fact table load I ran into this article on SQL Server Central article.  This article explains very well what an inferred member is.
This is a great article and I started using it but found the need to have some modifications.  The client I was working with rewrote the script component to fit these needs so with his permission I thought I would share it with you!  To make sure I give the proper credit thanks for the great script David Purdy!
The package below demonstrates a typical fact table load that performs lookups to join back to each dimension to return back to the proper surrogate key.  When that lookup does not return a key by default the package would fail but we are ignoring those nonmatches so that the next transform will pick them up.
In this solution the script component following the Lookup does a proper inferred member insert back into the dimension and returns the new key immediately after it is created.
The description property is used to centralize some of the logic for the insert and select required.  Here is the what is in the description:
insert into dbo.DimCustomer(CustomerAlternateKey,AuditIsInferred) Values(?,1);select CustomerKey from dbo.DimCustomer where CustomerAlternateKey = ?
This helps handle the insert into the dimension and returns the new surrogate key.  Notice that my example has a column called AuditIsInferred to identify if a record is inferred or not.  The example below shows a dimension with a single business key column but it you had multiples here is a template to follow:
insert into schemaname.tablename(businesskeycolumn1,businesskeycolumn2…,InferredColumn) Values(?,?…,1);select surrogatekey from schemaname.tablename where businesskeycolumn1 = ? and businesskeycolumn2 = ?…
You will need to ensure all columns are checked in the Input Columns page and then make your surrogate key readwrite because you will need to write and return back a value for this column.
You must also point to the correct connection for the dimension on the connection managers page.
Back on the Script page hit Edit Script and paste in the code below.  I’ve commented out instructions on how to adapt this code for your use.
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports System.Data.OleDb
Imports System.Collections
Imports System.Reflection
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain

    Inherits UserComponent

    'If more than one natural key is used uncomment another line below
    Private parmNatKeyIns1, parmNatKeySel1 As OleDbParameter
    'Private parmNatKeyIns2, parmNatKeySel2 As OleDbParameter
    'Private parmNatKeyIns3,parmNatKeySel3 As OleDbParameter
    'Private parmNatKeyIns4,parmNatKeySel4 As OleDbParameter

    Private objInsCommand, objSelCommand As OleDbCommand
    Private inputBuffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer
    Public htCache As New Generic.SortedDictionary(Of String, Integer)

    Private CacheHits As Integer = 0
    Private InferredMembers As Integer = 0
    Private ForcedLookups As Integer = 0
    Private NullNKs As Integer = 0

    Public Overrides Sub PreExecute()
        'This Sub initializes the SQL Connection and Stored Procedure

        objInsCommand = New OleDbCommand
        objSelCommand = New OleDbCommand

        'Define the command object
        With objInsCommand

            .CommandText = Me.ComponentMetaData.Description.Split(New Char() {";"c})(0) + ";select scope_identity()"
            .CommandType = CommandType.Text
            .Connection = New OleDbConnection(Connections.Connection.ConnectionString)

            'Change to the proper data type for the natural key.
            'If there is more than one natural key uncomment as many lines as necessary
            parmNatKeyIns1 = .Parameters.Add("@NK1", OleDbType.VarWChar, 15)
            'parmNatKeyIns2 = .Parameters.Add("@NK2", OleDbType.VarWChar, 3)
            'parmNatKeyIns3 = .Parameters.Add("@NK3", OleDbType.VarChar, 10)
            'parmNatKeyIns4 = .Parameters.Add("@NK4", OleDbType.VarChar, 10)


        End With

        'Define the select command object
        With objSelCommand

            .CommandText = Me.ComponentMetaData.Description.Split(New Char() {";"c})(1)
            .CommandType = CommandType.Text
            .Connection = New OleDbConnection(Connections.Connection.ConnectionString)

            'If more than one natural key is used uncomment another line below
            parmNatKeySel1 = .Parameters.Add(parmNatKeyIns1.ParameterName, parmNatKeyIns1.OleDbType, parmNatKeyIns1.Size)
            'parmNatKeySel2 = .Parameters.Add(parmNatKeyIns2.ParameterName, parmNatKeyIns2.DbType, parmNatKeyIns2.Size)
            'parmNatKeySel3 = .Parameters.Add(parmNatKeyIns3.ParameterName, parmNatKeyIns3.DbType, parmNatKeyIns3.Size)
            'parmNatKeySel4 = .Parameters.Add(parmNatKeyIns4.ParameterName, parmNatKeyIns4.DbType, parmNatKeyIns4.Size)

        End With
    End Sub

    Public Overrides Sub PostExecute()

        'Finalize expensive objects
        htCache = Nothing

        Dim sMessage As String = Me.ComponentMetaData.Name & " (Inferred Members = " + InferredMembers.ToString + ";Cache Hits = " + CacheHits.ToString + ";Forced Lookups = " + ForcedLookups.ToString + "; Null NKs = " & NullNKs.ToString & ")"
        Me.ComponentMetaData.FireInformation(0, Me.ComponentMetaData.Name, sMessage, "", 0, False)

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        'Change below to use the surrogate key column used for this lookup
        Dim SK_IsNull As Boolean = Row.CustomerKey_IsNull
        'Change below to use the natural key column used for this lookup.
        'If multiple natural keys were used uncomment before the 'Or' and add second column
        Dim NK_IsNull As Boolean = Row.CustomerAlternateKey_IsNull 'Or Row.xxx_IsNull 'Or Row.yyy_IsNull Or Row.zzz_IsNull
        'Change below to use the natural key column used for this lookup.
        'If multiple natural keys were used uncomment before the '+' and add second column
        Dim NK_CombinedKey As String = Row.CustomerAlternateKey.ToString '+ '+ Row.yyy.ToString + Row.zzz.ToString
        Dim SurrogateKey As Integer

        If NK_IsNull Then

            'Change below to use the surrogate key column used for this lookup
            Row.CustomerKey = -1
            NullNKs += 1

        ElseIf SK_IsNull Then

            If Not htCache.TryGetValue(NK_CombinedKey, SurrogateKey) Then

                'Change below to use the natural key column used for this lookup.
                'If multiple natural keys were used uncomment the next line add second column
                parmNatKeyIns1.Value = Row.CustomerAlternateKey : parmNatKeySel1.Value = parmNatKeyIns1.Value
                'parmNatKeyIns2.Value = : parmNatKeySel2.Value = parmNatKeyIns2.Value
                'parmNatKeyIns3.Value = Row.yyy : parmNatKeySel3.Value = parmNatKeyIns3.Value
                'parmNatKeyIns4.Value = Row.zzz : parmNatKeySel4.Value = parmNatKeyIns4.Value

                    SurrogateKey = CInt(objInsCommand.ExecuteScalar())
                    InferredMembers += 1
                Catch ex As Exception
                    SurrogateKey = CInt(objSelCommand.ExecuteScalar())
                    ForcedLookups += 1
                End Try
                htCache.Add(NK_CombinedKey, SurrogateKey)
                CacheHits += 1
            End If

            'Change below to use the surrogate key column used for this lookup
            Row.CustomerKey = SurrogateKey

        End If

    End Sub

End Class
Once this is done connect it back into your data flow stream and you should be prepared to handle inferred records now.  This process will need to be done for each lookup that you wish to allow inferred records in your dimensions.  Feel free to send me any questions or comments on how to improve it.  I’m definitely looking for ways to make it more dynamic or even a C# version of this!

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