SSIS - Inferred Member Insert in Fact Table Load

' 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
        MyBase.PreExecute()

        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)
            .Connection.Open()

            '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)

            .Prepare()

        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)
            .Connection.Open()

            '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)
            .Prepare()

        End With
    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()

        'Finalize expensive objects
        htCache = Nothing
        objInsCommand.Connection.Close()

        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.xxx.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 = Row.xxx : parmNatKeySel2.Value = parmNatKeyIns2.Value
                'parmNatKeyIns3.Value = Row.yyy : parmNatKeySel3.Value = parmNatKeyIns3.Value
                'parmNatKeyIns4.Value = Row.zzz : parmNatKeySel4.Value = parmNatKeyIns4.Value

                Try
                    SurrogateKey = CInt(objInsCommand.ExecuteScalar())
                    InferredMembers += 1
                Catch ex As Exception
                    SurrogateKey = CInt(objSelCommand.ExecuteScalar())
                    ForcedLookups += 1
                End Try
                htCache.Add(NK_CombinedKey, SurrogateKey)
            Else
                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