• State Verified Answer
  • Locked Locked
  • Replies 33 replies
  • Subscribers 95 subscribers
  • Views 35920 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Designer - SQL Database Import not Updating UNSAccountB

dshieldschk
over 10 years ago

Gurus,

I eluded to this question in a previous post and despite my best efforts, I can't find a solution yet. So here is the situation, one of the applications we are attesting to runs off an MS SQL Database and our DBAs have basically given the Quest account rights to run a few stored processes so that it can synch the users/groups from the SQL application to Quest. The script basically opens a connection string to the database, runs the stored procs and uses the data to populate users (one script) and populate data base roles (other script) into UNSAccountB and UNSGroupB respectively while tagging XProxyContext to be the application name... lets call it DBApp1 for privacy.

In the Production environment, the sccripts work just fine... they pull the data in and populate accordingly. I used the Database Transport tool to import all Change Labels into the Test lab. After verifying that AD synch was working Dev and then making sure that Manager > Unified Namespace has an entry for DBApp1, I ran the import. I watched the job logs intently - refreshing every few seconds, just to make sure everything worked. Yay! The script completed and I got NO errors. I even have the script writing a log file to show me what it does. The log file shows several new inserts into the database. Yay!

THEN... I checked UNSAccountB and UNSGroupB. NOTHING is there... There are no records at all...

I tried the same process again with DBApp2. Again, all things look great! Imports run and NOTHING in UNSAccountB or UNSGroupB. What the heck happened? Where did this stuff go? If the logs think everything is fine and the database is sending data (I verified this). Why doesn't it populate into Quest? Can anyone provide me some insight?

Thanks in advance!

  • 0 over 10 years ago

    Hi Dave,

    it seems to be an error of observation .

    Unfortunately it's not possible to suggest anything without knowing the import script - could you post it?

    Additional an example logfile would be helpful.

    Everything from the "problem environment".

    Steffen

  • 0 over 10 years ago

    Alright Steffen. Here is the first script - the user import script. I have sanitized portions of it to protect the innocent but the entire contents are there. 

    #If Not SCRIPTDEBUGGER Then
    References VI.DataImport.dll
    Imports System.Collections.Generic
    Imports System.IO
    Imports System.Globalization
    Imports VI.DB.Specialized
    Imports VI.DataImport
    #End If
    Public Sub TST_DBApp2_Import(ByVal dfImport As DataFileImport)
        ' Standard variables
        Dim logSection As IDisposable
        Dim milliSec As Double = Timer
              Dim logFile As String =Connection.GetConfigParm("Custom\LogFileOutputDirectory") & "\TST_DBApp2_Import_" & Year(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now) & milliSec.ToString & ".log"
        Dim f As ISqlFormatter = Connection.SqlFormatter
              ' Table
        Dim table As TableDef = Connection.Tables("UNSAccountB")
              ' Do not delete any objects, if this marker is set
              Dim doNotDelete As Boolean = False
              ' Line counter
        Dim counter As New LineCounter()
              Dim obj As ISingleDbObject = Nothing
              ' Value provider
              Dim lineData As New LineValueProvider(New String() {"User ID", "User Name", "User Last Updated By", "User Last Updated On", "", "", ""})
              Provider = lineData
              ' Import from an external database
              Dim lineProvider As New DbLineProvider()
              lineProvider.ProviderName = "System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
              lineProvider.ConnectionString = Connection.GetConfigParm("Custom\DBApp2\ConnectionString")
              ' SQL statement
              Dim statement As New PlainSqlStatement()
              statement.Statement = "SELECT U.UserId AS [User ID], U.UserName AS [User Name], U.LastUpdateID AS [User Last Updated By], U.LastUpdated AS [User Last Updated On] FROM TST..ssMasUser U"
      
              lineProvider.Statement = statement
      
              '
        ' Configure column resolution
              '
      
        ' Column indices
        Const iAccountName As Integer = 0
        Const icn As Integer = 1
              ' Resolver to get column data from import data
        Dim columnAccountName As IResolveImportValue = New ResolveImportValueSimple("AccountName")
        Dim columncn As IResolveImportValue = New ResolveImportValueSimple("cn")
              ' Build a dictionary: The last parameter forces exceptions when duplicate keys are found
        Dim columnUID_Person As New ResolveImportValueHashed( _
            Connection, _
            ObjectWalker.ColDefs(table, "FK(UID_Person).CentralAccount"), False)
              ' Build a dictionary: The last parameter forces exceptions when duplicate keys are found
        Dim columnUID_UNSContainerB As New ResolveImportValueHashed( _
            Connection, _
            ObjectWalker.ColDefs(table, "FK(UID_UNSContainerB).canonicalname"), False)
        Dim columnXProxyContext As IResolveImportValue = New ResolveImportValueSimple("XProxyContext")
              ' Set connection variables
              Connection.Variables("FULLSYNC") = "true"
        Try
            '
            ' Create a dictionary for the destination table
            '
            Dim colImport As IColDbObject = Connection.CreateCol("UNSAccountB")
            Dim elemsByKey As New Dictionary(Of String, IColElem)(StringComparer.OrdinalIgnoreCase)
                        logSection = dfImport.LogSection(#LD("Bilde Index über bestehende Daten")#)
                        Try
                                  ' Set keys and values as display items to load them with the collection
                                  colImport.Prototype("AccountName").IsDisplayItem = True
                                  colImport.Prototype("cn").IsDisplayItem = True
                                  colImport.Prototype("UID_Person").IsDisplayItem = True
                                  colImport.Prototype("UID_UNSContainerB").IsDisplayItem = True
                                  colImport.Prototype("XProxyContext").IsDisplayItem = True
                                  colImport.Prototype.WhereClause = "xproxycontext = N'DBApp2'"
      
                                  ' Load the collection
                                  colImport.Load(CollectionLoadType.Slim)
                                  ' Put the entries in the dictionary
                                  For Each elem As IColElem In colImport
                                            Dim key As String
                                            key = elem.GetValue("AccountName").ToString
                                            'VID_Write2Log(logFile,String.Format("Processing key: {0}",key))
                                            If elemsByKey.ContainsKey(key) Then
                                                      VID_Write2Log(logFile,String.Format("Duplicate key: {0}",key))
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Duplicate key: {0}", key)#)
                                            Else
                                                      'VID_Write2Log(logFile,String.Format("Adding key {0} to elemsByKey",key))
                                                      elemsByKey.Add(key, elem)
                                            End If
                                  Next
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
      
                        'VID_Write2Log(logFile,String.Format("---------------Finished processing {0} keys",elemsByKey.Count))
                        logSection = dfImport.LogSection(#LD("Import data from file")#)
            Try
                                  For Each line As Line In lineProvider.GetLines(counter)
                                            ' Pay attention to the Stop flag
                                            If StopProcessing Then
                                                      dfImport.Log(MsgSeverity.Warning, #LD("Abarbeitung wurde gestoppt.")#)
                                                      Exit For
                                            End If 
      
                                            Try
                                                      ' Get raw data from the current line
                                                      Dim key As String
                                                      Dim valAccountName As String
                                                      Dim resAccountName As String
                                                      Dim valcn As String
                                                      Dim rescn As String
                                                      Dim valUID_Person As String
                                                      Dim resUID_Person As String
                                                      Dim valUID_UNSContainerB As String
                                                      Dim resUID_UNSContainerB As String
                                                      Dim valXProxyContext As String
                                                      Dim resXProxyContext As String
                                                                valAccountName = line.GetValue(iAccountName)
                                                                valcn = line.GetValue(icn)
                                                                valUID_Person = line.GetValue(iAccountName)
                                                                valUID_UNSContainerB = "DBApp2/Users"
                                                                valXProxyContext = "DBApp2"
                                                                '
                                                                ' Here is the place to check and change raw data
                                                                '
                                                                lineData.Line = line
      
                                                                Value = valUID_Person
      
                                                                ' Start of convert script for UID_Person
                                                                Value = line.GetValue(iAccountName)
                                                                ' End of convert script for UID_Person
      
                                                                valUID_Person = DbVal.ConvertTo(Of String)(Value)
      
      
                                                                ' Convert raw data to final column data values
                                                                resAccountName = DbVal.ConvertTo(Of String)(columnAccountName.ResolveValue(valAccountName), lineProvider.Culture)
                                                                rescn = DbVal.ConvertTo(Of String)(columncn.ResolveValue(valcn), lineProvider.Culture)
                                                                resUID_Person = DbVal.ConvertTo(Of String)(columnUID_Person.ResolveValue(valUID_Person), lineProvider.Culture)
                                                                resUID_UNSContainerB = DbVal.ConvertTo(Of String)(columnUID_UNSContainerB.ResolveValue(valUID_UNSContainerB), lineProvider.Culture)
                                                                resXProxyContext = DbVal.ConvertTo(Of String)(columnXProxyContext.ResolveValue(valXProxyContext), lineProvider.Culture)
                                                                resUID_Person=Connection.GetSingleProperty("Person", "UID_Person", f.Comparison("CentralAccount", valUID_Person , ValType.String)).ToString
      
                                                                VID_Write2Log(logFile,String.Format("Got referenced uid person as: {0}",resUID_Person))
                                                                '
                                                                ' Here is the place to check and change final column data
                                                                '
                                                                '
                                                                ' Here starts the standard handling. It is recommended to not change anything beyond this point.
                                                                '
                                                                '
                                                                ' Create key for this line
                                                                '
                                                                key = DbVal.ConvertTo(Of String)(resAccountName).ToString
                                                                key = key.Trim
                                                                'VID_Write2Log(logFile,String.Format("key read from DB: {0}",key))
                                                      obj = Nothing
                                                      Dim elem As IColElem = Nothing
                                                      Dim bag As New PropertyBag()
      
                                                      If elemsByKey.TryGetValue(key, elem) Then
                                                                '
                                                                ' Found -> Update
                                                                '
      
                                                                VID_Write2Log(logFile,String.Format("Updating {0}",key))
                                                                ' Element in der Collection für die Übermengenbehandlung markieren
                                                                elem.IsSelected = True
                                                                ' Map values
                                                                Dim found As Boolean = False
                                                                'If DbVal.Compare(elem.GetRaw("cn"), rescn, ValType.String) <> 0 Then
                                                                '          bag.PutValue("cn", rescn)
                                                                '          found = True
                                                                'End If
                                                                If DbVal.Compare(elem.GetRaw("UID_Person"), resUID_Person, ValType.String) <> 0 Then
                                                                          bag.PutValue("UID_Person", resUID_Person)
                                                                          found = True
                                                                End If
                                                                'If DbVal.Compare(elem.GetRaw("UID_UNSContainerB"), resUID_UNSContainerB, ValType.String) <> 0 Then
                                                                '          bag.PutValue("UID_UNSContainerB", resUID_UNSContainerB)
                                                                '          found = True
                                                                'End If
                                                                'If DbVal.Compare(elem.GetRaw("XProxyContext"), resXProxyContext, ValType.String) <> 0 Then
                                                                '          bag.PutValue("XProxyContext", resXProxyContext)
                                                                '          found = True
                                                                'End If
                                                                If found Then
                                                                          ' Create object only when values have changed 
                                      
                                                                          obj = elem.Create()
                                                                          If Not obj.IsLoaded Then
                                                                                    obj.Load()
                                                                          End If
                                                                Else
                                                                          counter.Increment(LineType.Unchanged)
                                                                End If
                                                      Else
                                                                '
                                                                ' Not found -> Insert
                                                                '
                                                                VID_Write2Log(logFile,String.Format("Creating user {0}",resAccountName))
                                                                obj = Connection.CreateSingle("UNSAccountB")
                                                                ' Fill keys and values
                                                                bag.PutValue("AccountName", resAccountName)
                                                                bag.PutValue("cn", rescn)
                                                                If resAccountName.ToUpper.StartsWith("Special") Or resAccountName.ToUpper.StartsWith("Control") Then
                                                                          VID_Write2Log(logFile,String.Format("Skipping person association for {0}",resAccountName))
                                                                Else
                                                                          bag.PutValue("UID_Person", resUID_Person)
                                                                End If
                                                                bag.PutValue("UID_UNSContainerB", resUID_UNSContainerB)
                                                                bag.PutValue("XProxyContext", resXProxyContext)
                                                      End If
                                                      '
                                                      ' Additional values can be put into the bag here
                                                      '
      
                                                      If Not obj Is Nothing Then
                                                                If obj.IsDeleted Then
                                                                          ' Recall deleted object
                                                                          obj.Recall()
                                                                End If
                                                                ' Change object
                                                                bag.ChangeObject(obj, True)
                                                                Dim isUpdate As Boolean = obj.IsLoaded
      
                                                                ' Save it
                                                                If obj.IsDifferent Then
                                                                          obj.Save()
      
                                                                          If isUpdate Then
                                                                                    counter.Increment(LineType.Updated)
                                                                          Else
                                                                                    counter.Increment(LineType.Inserted)
                                                                          End If
                                                                Else
                                                                          counter.Increment(LineType.Unchanged)
                                                                End If
                                                                If elem Is Nothing Then
                                                                          ' Include in our collection to avoid double imports of elements
                                                                          Dim keyInsert As String
                                                                          keyInsert = obj.GetValue("AccountName").String
                                                                          colImport.Insert(obj)
                                                                          Dim elemInsert As IColElem = colImport(colImport.Count - 1)
                                                                          elemsByKey(keyInsert) = elemInsert
                                                                          elemInsert.IsSelected = True
                                                                End If
                                                      End If
                                            Catch ex As Exception
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Error in line {0}: {1}", counter(LineType.Total), ViException.ErrorString(ex))#)
                              doNotDelete = True
                                                      counter.Increment(LineType.Error)
                                            End Try
                                            If (counter(LineType.Total) Mod 100) = 0 Then
                                                      dfImport.SetProgressInfo(#LD("{0} lines imported, {1} ignored, {2} inserted, {3} updated, {4} errors", counter(LineType.Total), counter(LineType.Unchanged), counter(LineType.Inserted), counter(LineType.Updated), counter(LineType.Error))#)
                                            End If
                                  Next ' ForEach line
      
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
                        '
            ' Superset handling
            '
            If StopProcessing Then
                                  dfImport.Log(MsgSeverity.Warning, #LD("No superset handling, because import was stopped.")#)
                        ElseIf doNotDelete Then
                                  dfImport.Log(MsgSeverity.Warning, #LD("There were errors during the parsing of input data. No elements will be deleted to prevent data loss.")#)
                        Else 
                                  logSection = dfImport.LogSection(#LD("Removing entries missing in the import")#)
                                  Try
                                            Dim toDelete As New List(Of IColElem)()
                                            For Each elem As IColElem In colImport
                                                      Try
                                                                If elem.IsSelected Then
                                                                          Continue For   ' Found in the import
                                                                End If
                                                                VID_Write2Log(logFile,String.Format("Marking for removal {0}",elem.GetValue("AccountName")))
                                                                toDelete.Add(elem)
                                                      Catch ex As Exception
                                                                dfImport.Log(MsgSeverity.Serious, #LD("Error removing object {0}", elem.Display)#)
                                                                dfImport.LogExecption(ex)
                                                      End Try
                                            Next
                                            For Each elem As IColElem In toDelete
                                                      Dim display As String = Nothing          ' There are no displays in slim collections
                                                      Try
                                                                obj = elem.Create()
                                                                display = obj.Display
                                                                obj.Delete()
                                                                obj.Save()
                                                                VID_Write2Log(logFile,String.Format("Executed removal ok"))
                                                                counter.Increment(LineType.Deleted)
                                                                dfImport.SetProgressInfo(#LD("{0} of {1} deleted", counter(LineType.Deleted), toDelete.Count)#)
                                                      Catch ex As Exception
                                                                If String.IsNullOrEmpty(display) Then
                                                                          display = New DbObjectKey(table, elem).ToXmlString()
                                                                End If
                                                                dfImport.Log(MsgSeverity.Serious, #LD("Error deleting object {0}", display)#)
                                                                dfImport.LogExecption(ex)
      
                                                                counter.Increment(LineType.Error)
                                                      End Try
                                            Next
                                  Finally
                                            If Not logSection Is Nothing Then 
                                                      logSection.Dispose()
                                                      logSection = Nothing
                                            End If
                                  End Try
                        End If
        Finally
            
                        Connection.Variables.Remove("FULLSYNC")
        End Try
              Using dfImport.LogSection(#LD("Resultate")#)
                        dfImport.Log(#LD("{0} lines imported", counter(LineType.Total))#)
                        dfImport.Log(#LD("{0} header lines", counter(LineType.Header))#)
                        dfImport.Log(#LD("{0} inserted", counter(LineType.Inserted))#)
                        dfImport.Log(#LD("{0} changed", counter(LineType.Updated))#)
                        dfImport.Log(#LD("{0} deleted", counter(LineType.Deleted))#)
                        dfImport.Log(#LD("{0} not changed", counter(LineType.Unchanged))#)
                        dfImport.Log(#LD("{0} not found", counter(LineType.NotFound))#)
                        dfImport.Log(#LD("{0} empty lines", counter(LineType.Empty))#)
                        dfImport.Log(#LD("{0} errors", counter(LineType.Error))#)
              End Using
    End Sub
  • 0 over 10 years ago

    Hi Dave,

    strange ...

    The script looks good - there are no transactions in it - so you don't have to "commit" any transactions.

    And if there would appear any errors during the saving of the objects you would get an information on the "error counter".

    So I think the objects were created in the database - the question is: In WHICH database?

    The job "DataImport" (which executes the script for the import) contains the parameter "ConnectionString".

    On this parameter the target database for the import is defined.

    Normally the parameter contains:

    Value = VID_GetValueOfDialogdatabases("ConnectionString")

    ... that means the connectionstring comes from the table "DialogDatabase", column "ConnectionString".

    I suggest to check this parameter in the import job - and if it contains the mentioned default value you should check the value in "DialogDatabase.ConnectionString".

    Maybe your import affected another db...

    No other idea currently.

    Regards,

    Steffen

  • 0 over 10 years ago

    Thanks Steffen, I checked the VID_GetValueofDialogDatabases("ConnectionString") and that has not been changed. But here's a thought. The particular script I posted is only ONE piece of the entire import for DBApp2. Perhaps the issue lies in one of the other scripts that is part of the process orchestration. The entire import process follows the steps below:

    1.) Run Script TST_DBApp2_Container Import

    2.) Run Script TST_DBApp2_Group Import

    3.) Run Script TST_DBApp2_User Import

    4.) Run Script TST_DBApp2_UNSAccountBInUNSGroupB Import

    So maybe the issue is starting somewhere earlier than the User import. That being said, here are the other scripts:

    Container Import:

    #If Not SCRIPTDEBUGGER Then
    References VI.DataImport.dll
    Imports System.Collections.Generic
    Imports System.IO
    Imports System.Globalization
    Imports VI.DB.Specialized
    Imports VI.DataImport
    #End If
    Public Sub TST_DBApp2_Import(ByVal dfImport As DataFileImport)
        ' Standard variables
        Dim logSection As IDisposable
        ' Table
        Dim table As TableDef = Connection.Tables("UNSContainerB")
              Dim logFile As String = Connection.GetConfigParm("Custom\LogFileOutputDirectory") & "\TST_DBApp2_Container_Import" & Year(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now) & ".log"
              ' Line counter
        Dim counter As New LineCounter()
              ' Value provider
              Dim lineData As New LineValueProvider(New String() {"Role ID", "Role Name", "Role Last Updated By", "Role Last Updated On", "", ""})
              Provider = lineData
              ' Import from an external database
              Dim lineProvider As New DbLineProvider()
              lineProvider.ProviderName = "System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
              lineProvider.ConnectionString = Connection.GetConfigParm("Custom\DBApp2\ConnectionString")
              ' SQL statement
              Dim statement As New PlainSqlStatement()
              statement.Statement = "SELECT R.RoleTID AS [Role ID] " & _
    "       ,R.RoleName AS [Role Name] " & _
    "       ,R.LastUpdateID AS [Role Last Updated By] " & _
    "       ,R.LastUpdated AS [Role Last Updated On] " & _
    "FROM TST..ssMasRole R " & _
    "WHERE R.AutoGenerated=0"
              lineProvider.Statement = statement
              '
        ' Configure column resolution
              '
        ' Column indices
        Const icn As Integer = 1
              ' Resolver to get column data from import data
        Dim columncn As IResolveImportValue = New ResolveImportValueSimple("cn")
              ' Build a dictionary: The last parameter forces exceptions when duplicate keys are found
        Dim columnUID_ParentUNSContainerB As New ResolveImportValueHashed( _
            Connection, _
            ObjectWalker.ColDefs(table, "FK(UID_ParentUNSContainerB).canonicalname"), False)
        Dim columnXProxyContext As IResolveImportValue = New ResolveImportValueSimple("XProxyContext")
              ' Set connection variables
              Connection.Variables("FULLSYNC") = "true"
        Try
            '
            ' Create a dictionary for the destination table
            '
            Dim colImport As IColDbObject = Connection.CreateCol("UNSContainerB")
            Dim elemsByKey As New Dictionary(Of String, IColElem)(StringComparer.OrdinalIgnoreCase)
                        logSection = dfImport.LogSection(#LD("Bilde Index über bestehende Daten")#)
                        Try
                                  ' Set keys and values as display items to load them with the collection
                                  colImport.Prototype("cn").IsDisplayItem = True
                                  colImport.Prototype("UID_ParentUNSContainerB").IsDisplayItem = True
                                  colImport.Prototype("XProxyContext").IsDisplayItem = True
                                  colImport.Prototype.WhereClause = "xproxycontext = N'DBApp2'"
                                  ' Load the collection
                                  colImport.Load(CollectionLoadType.Slim)
                                  ' Put the entries in the dictionary
                                  For Each elem As IColElem In colImport
                                            Dim key As String
                                            key = elem.GetValue("cn").String
                                            If elemsByKey.ContainsKey(key) Then
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Duplicate key: {0}", key)#)
                                            Else
                                                      elemsByKey.Add(key, elem)
                                            End If
                                  Next
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
              VID_Write2Log(logFile,String.Format("Loaded {0} records",colImport.Count))
                        logSection = dfImport.LogSection(#LD("Import data from file")#)
            Try
                                  For Each line As Line In lineProvider.GetLines(counter)
                                            ' Pay attention to the Stop flag
                                            If StopProcessing Then
                                                      dfImport.Log(MsgSeverity.Warning, #LD("Abarbeitung wurde gestoppt.")#)
                                                      Exit For
                                            End If 
                                            Try
                                                      ' Get raw data from the current line
                                                      Dim key As String
                                                      Dim valcn As String
                                                      Dim rescn As String
                                                      Dim valUID_ParentUNSContainerB As String
                                                      Dim resUID_ParentUNSContainerB As String
                                                      Dim valXProxyContext As String
                                                      Dim resXProxyContext As String
                                                                valcn = line.GetValue(icn)
                                                                valUID_ParentUNSContainerB = "DBApp2/Groups"
                                                                valXProxyContext = "DBApp2"
                                                                '
                                                                ' Here is the place to check and change raw data
                                                                '
                                                                lineData.Line = line
                                                                Value = valcn
                                                                VID_Write2Log(logFile,String.Format("Read from file {0} ",valcn))
                                                                ' Start of convert script for cn
                                                                Dim tmp() As String = CStr(Value).Split("-"C)
                                                                If tmp.Length > 1 Then
                                                                          Value = tmp(0).Trim 
                                                                End If
                                                                ' End of convert script for cn
                                                                valcn = DbVal.ConvertTo(Of String)(Value) 
                                                                VID_Write2Log(logFile,String.Format("After checkin is root {0} ",valcn))
                                                                ' Convert raw data to final column data values
                                                                rescn = DbVal.ConvertTo(Of String)(columncn.ResolveValue(valcn), lineProvider.Culture)
                                                                resUID_ParentUNSContainerB = DbVal.ConvertTo(Of String)(columnUID_ParentUNSContainerB.ResolveValue(valUID_ParentUNSContainerB), lineProvider.Culture)
                                                                resXProxyContext = DbVal.ConvertTo(Of String)(columnXProxyContext.ResolveValue(valXProxyContext), lineProvider.Culture)
                                                                '
                                                                ' Here is the place to check and change final column data
                                                                '
                                                                '
                                                                ' Here starts the standard handling. It is recommended to not change anything beyond this point.
                                                                '
                                                                '
                                                                ' Create key for this line
                                                                '
                                                                key = DbVal.ConvertTo(Of String)(rescn)
                                                                VID_Write2Log(logFile,String.Format("Processing key {0} ",key))
                                                      Dim obj As ISingleDbObject = Nothing
                                                      Dim elem As IColElem = Nothing
                                                      Dim bag As New PropertyBag()
                                                      If elemsByKey.TryGetValue(key, elem) Then
                                                                '
                                                                ' Found -> Update
                                                                '
                                                                VID_Write2Log(logFile,String.Format("UPDATE: key {0} ",key))
                                                                ' Element in der Collection für die Übermengenbehandlung markieren
                                                                elem.IsSelected = True
                                                                ' Map values
                                                                Dim found As Boolean = False
                                                                If DbVal.Compare(elem.GetRaw("UID_ParentUNSContainerB"), resUID_ParentUNSContainerB, ValType.String) <> 0 Then
                                                                          bag.PutValue("UID_ParentUNSContainerB", resUID_ParentUNSContainerB)
                                                                          found = True
                                                                End If
                                                                If DbVal.Compare(elem.GetRaw("XProxyContext"), resXProxyContext, ValType.String) <> 0 Then
                                                                          bag.PutValue("XProxyContext", resXProxyContext)
                                                                          found = True
                                                                End If
                                                                If found Then
                                                                          ' Create object only when values have changed 
                                                                          obj = elem.Create()
                                                                          If Not obj.IsLoaded Then
                                                                                    obj.Load()
                                                                          End If
                                                                Else
                                                                          counter.Increment(LineType.Unchanged)
                                                                End If
                                                      Else 
                                                                '
                                                                ' Not found -> Insert
                                                                '
                                                                VID_Write2Log(logFile,String.Format("INSERT: key {0} ",key))
                                                                obj = Connection.CreateSingle("UNSContainerB")
                                                                ' Fill keys and values
                                                                bag.PutValue("cn", rescn)
                                                                bag.PutValue("UID_ParentUNSContainerB", resUID_ParentUNSContainerB)
                                                                bag.PutValue("XProxyContext", resXProxyContext)
                                                      End If
                                                      '
                                                      ' Additional values can be put into the bag here
                                                      '
                                                      If Not obj Is Nothing Then
                                                                If obj.IsDeleted Then
                                                                          ' Recall deleted object
                                                                          obj.Recall()
                                                                End If
                                                                ' Change object
                                                                bag.ChangeObject(obj, True)
                                                                Dim isUpdate As Boolean = obj.IsLoaded
                                                                ' Save it
                                                                If obj.IsDifferent Then
                                                                          obj.Save()
                                                                          If isUpdate Then
                                                                                    counter.Increment(LineType.Updated)
                                                                          Else
                                                                                    counter.Increment(LineType.Inserted)
                                                                          End If
                                                                Else
                                                                          counter.Increment(LineType.Unchanged)
                                                                End If
                                                                If elem Is Nothing Then
                                                                          ' Include in our collection to avoid double imports of elements
                                                                          Dim keyInsert As String
                                                                          keyInsert = obj.GetValue("cn").String
                                                                          colImport.Insert(obj)
                                                                          Dim elemInsert As IColElem = colImport(colImport.Count - 1)
                                                                          elemsByKey(keyInsert) = elemInsert
                                                                          elemInsert.IsSelected = True
                                                                End If
                                                                ' Update index for hierarchies
                                                                columnUID_ParentUNSContainerB.AddOrUpdate(obj)
                                                      End If
                                            Catch ex As Exception
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Error in line {0}: {1}", counter(LineType.Total), ViException.ErrorString(ex))#)
                                                      counter.Increment(LineType.Error)
                                            End Try
                                            If (counter(LineType.Total) Mod 100) = 0 Then
                                                      dfImport.SetProgressInfo(#LD("{0} lines imported, {1} ignored, {2} inserted, {3} updated, {4} errors", counter(LineType.Total), counter(LineType.Unchanged), counter(LineType.Inserted), counter(LineType.Updated), counter(LineType.Error))#)
                                            End If
                                  Next ' ForEach line
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
                        '
                        ' No superset handling
                        '
        Finally
                        Connection.Variables.Remove("FULLSYNC")
        End Try
              Using dfImport.LogSection(#LD("Resultate")#)
                        dfImport.Log(#LD("{0} lines imported", counter(LineType.Total))#)
                        dfImport.Log(#LD("{0} header lines", counter(LineType.Header))#)
                        dfImport.Log(#LD("{0} inserted", counter(LineType.Inserted))#)
                        dfImport.Log(#LD("{0} changed", counter(LineType.Updated))#)
                        dfImport.Log(#LD("{0} deleted", counter(LineType.Deleted))#)
                        dfImport.Log(#LD("{0} not changed", counter(LineType.Unchanged))#)
                        dfImport.Log(#LD("{0} not found", counter(LineType.NotFound))#)
                        dfImport.Log(#LD("{0} empty lines", counter(LineType.Empty))#)
                        dfImport.Log(#LD("{0} errors", counter(LineType.Error))#)
              End Using
    End Sub

    Group Import

    #If Not SCRIPTDEBUGGER Then
    References VI.DataImport.dll
    Imports System.Collections.Generic
    Imports System.IO
    Imports System.Globalization
    Imports VI.DB.Specialized
    Imports VI.DataImport
    #End If
    Public Sub TST_DBApp2Group_Import(ByVal dfImport As DataFileImport)
        ' Standard variables
        Dim logSection As IDisposable
        
        ' Table
        Dim table As TableDef = Connection.Tables("UNSGroupB")
              ' Line counter
        Dim counter As New LineCounter()
              ' Do not delete any objects, if this marker is set
              Dim doNotDelete As Boolean = False
              Dim logFile As String = Connection.GetConfigParm("Custom\LogFileOutputDirectory") & "\TST_DBApp2_Group_Import_" & Year(Now) & Day(Now) & Hour(Now) & Minute(Now) & Second(Now)
              Dim obj As ISingleDbObject = Nothing
              ' Value provider
              Dim lineData As New LineValueProvider(New String() {"Role ID", "Role Name", "Role Last Updated By", "Role Last Updated On", "", ""})
              Provider = lineData
              ' Import from an external database
              Dim lineProvider As New DbLineProvider()
              lineProvider.ProviderName = "System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
              lineProvider.ConnectionString = Connection.GetConfigParm("Custom\DBApp2\ConnectionString")
              ' SQL statement
              Dim statement As New PlainSqlStatement()
              statement.Statement = "SELECT R.RoleTID AS [Role ID] " & _
    "       ,R.RoleName AS [Role Name] " & _
    "       ,R.LastUpdateID AS [Role Last Updated By] " & _
    "       ,R.LastUpdated AS [Role Last Updated On] " & _
    "FROM TST..ssMasRole R " & _
    "WHERE R.AutoGenerated=0"
      
              lineProvider.Statement = statement
      
              '
        ' Configure column resolution
              '
      
        ' Column indices
        Const icn As Integer = 1
        Const iObjectGUID As Integer = 0
              ' Resolver to get column data from import data
        Dim columnCanonicalName As IResolveImportValue = New ResolveImportValueSimple("CanonicalName")
        Dim columncn As IResolveImportValue = New ResolveImportValueSimple("cn")
        Dim columnObjectGUID As IResolveImportValue = New ResolveImportValueSimple("ObjectGUID")
        Dim columnXProxyContext As IResolveImportValue = New ResolveImportValueSimple("XProxyContext")
              Dim columnUID_UNSContainerB As New ResolveImportValueHashed( _
            Connection, _
            ObjectWalker.ColDefs(table, "FK(UID_UNSContainerB).canonicalname"), False)
              ' Set connection variables
              Connection.Variables("FULLSYNC") = "true"
        Try
            '
            ' Create a dictionary for the destination table
            '
            Dim colImport As IColDbObject = Connection.CreateCol("UNSGroupB")
            Dim elemsByKey As New Dictionary(Of String, IColElem)(StringComparer.OrdinalIgnoreCase)
                        logSection = dfImport.LogSection(#LD("Bilde Index über bestehende Daten")#)
                        Try
                                  ' Set keys and values as display items to load them with the collection
                                  colImport.Prototype("CanonicalName").IsDisplayItem = True
                                  colImport.Prototype("cn").IsDisplayItem = True
                                  colImport.Prototype("ObjectGUID").IsDisplayItem = True
                                  colImport.Prototype("XProxyContext").IsDisplayItem = True
                                  colImport.Prototype("UID_UNSContainerB").IsDisplayItem = True
                                  colImport.Prototype.WhereClause = "xproxycontext = N'DBApp2'"
      
                                  ' Load the collection
                                  colImport.Load(CollectionLoadType.Slim)
                                  ' Put the entries in the dictionary
                                  For Each elem As IColElem In colImport
                                            Dim key As String
                                            key = elem.GetValue("ObjectGUID").String
                                            If elemsByKey.ContainsKey(key) Then
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Duplicate key: {0}", key)#)
                                            Else
                                                      elemsByKey.Add(key, elem)
                                            End If
                                  Next
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
                        VID_Write2Log(logFile,String.Format("Got {0} existing UNSGroupB records", elemsByKey.Count))
                        logSection = dfImport.LogSection(#LD("Import data from file")#)
            Try
                                  For Each line As Line In lineProvider.GetLines(counter)
                                            ' Pay attention to the Stop flag
                                            If StopProcessing Then
                                                      dfImport.Log(MsgSeverity.Warning, #LD("Abarbeitung wurde gestoppt.")#)
                                                      Exit For
                                            End If 
      
                                            Try
                                                      ' Get raw data from the current line
                                                      Dim key As String
                                                      Dim valCanonicalName As String
                                                      Dim resCanonicalName As String
                                                      Dim valcn As String
                                                      Dim rescn As String
                                                      Dim valObjectGUID As String
                                                      Dim resObjectGUID As String
                                                      Dim valXProxyContext As String
                                                      Dim resXProxyContext As String
                                                      Dim resUID_UNSContainerB As String
                                                      Dim valUID_UNSContainerB As String
                                                                valUID_UNSContainerB = "DBApp2/Groups"
                                                                valcn = line.GetValue(icn)
                                                                valObjectGUID = line.GetValue(iObjectGUID)
                                                                valXProxyContext = "DBApp2"
                                                                '
                                                                ' Here is the place to check and change raw data
                                                                '
                                                                lineData.Line = line
      
                                                                Value = valcn
      
                                                                ' Start of convert script for CanonicalName
                                                                Dim tmp() As String = CStr(Value).Split("-"C)
      
                                                                If tmp.Length > 1 Then
                                                                          Value = valUID_UNSContainerB & "/" & tmp(0).Trim 
                                                                          valUID_UNSContainerB = DbVal.ConvertTo(Of String)(Value).ToString.Trim
                                                                End If
      
                                                                ' End of convert script for CanonicalName
      
      
                                                                Value = valcn
      
                                                                ' Start of convert script for cn
      
                                                                tmp = CStr(Value).Split("-"C)
                                                                If tmp.Length > 1 Then
                                                                          Value = tmp(1).Trim 
                                                                End If
      
      
                                                                ' End of convert script for cn
      
                                                                valcn = DbVal.ConvertTo(Of String)(Value).ToString.Trim
                                                          
      
                                                                ' Convert raw data to final column data values
                                                                resCanonicalName = DbVal.ConvertTo(Of String)(columnCanonicalName.ResolveValue(valCanonicalName), lineProvider.Culture)
                                                                rescn = DbVal.ConvertTo(Of String)(columncn.ResolveValue(valcn), lineProvider.Culture)
                                                                resObjectGUID = DbVal.ConvertTo(Of String)(columnObjectGUID.ResolveValue(valObjectGUID), lineProvider.Culture)
                                                                resXProxyContext = DbVal.ConvertTo(Of String)(columnXProxyContext.ResolveValue(valXProxyContext), lineProvider.Culture)
                                                                resUID_UNSContainerB = DbVal.ConvertTo(Of String)(columnUID_UNSContainerB.ResolveValue(valUID_UNSContainerB), lineProvider.Culture)
                                                                '
                                                                ' Here is the place to check and change final column data
                                                                '
                                                                '
                                                                ' Here starts the standard handling. It is recommended to not change anything beyond this point.
                                                                '
                                                                '
                                                                ' Create key for this line
                                                                '
                                                                key = DbVal.ConvertTo(Of String)(resObjectGUID)
                                                      obj = Nothing
                                                      Dim elem As IColElem = Nothing
                                                      Dim bag As New PropertyBag()
                                                      If elemsByKey.TryGetValue(key, elem) Then
                                                                '
                                                                ' Found -> Update
                                                                '
                                                                VID_Write2Log(logFile,String.Format("UPDATE on {0}", key))
                                                                ' Element in der Collection für die Übermengenbehandlung markieren
                                                                elem.IsSelected = True
                                                                ' Map values
                                                                Dim found As Boolean = False
                                                                'If DbVal.Compare(elem.GetRaw("CanonicalName"), resCanonicalName, ValType.String) <> 0 Then
                                                      '                    bag.PutValue("CanonicalName", resCanonicalName)
                                                      '                    found = True
                                                      '          End If
                                                                If DbVal.Compare(elem.GetRaw("cn"), rescn, ValType.String) <> 0 Then
                                                                          bag.PutValue("cn", rescn)
                                                                          found = True
                                                                End If
                                                                If DbVal.Compare(elem.GetRaw("XProxyContext"), resXProxyContext, ValType.String) <> 0 Then
                                                                          bag.PutValue("XProxyContext", resXProxyContext)
                                                                          found = True
                                                                End If
      
                                                                If DbVal.Compare(elem.GetRaw("UID_UNSContainerB"), resUID_UNSContainerB, ValType.String) <> 0 Then
                                                                          bag.PutValue("UID_UNSContainerB", resUID_UNSContainerB)
                                                                          found = True
                                                                End If
                                                                If found Then
                                                                          ' Create object only when values have changed 
                                      
                                                                          obj = elem.Create()
                                                                          If Not obj.IsLoaded Then
                                                                                    obj.Load()
                                                                          End If
                                                                Else
                                                                          counter.Increment(LineType.Unchanged)
                                                                End If
                                                      Else
                                                                '
                                                                ' Not found -> Insert
                                                                '
                                                                VID_Write2Log(logFile,String.Format("INSERT on {0}", key))
                                                                obj = Connection.CreateSingle("UNSGroupB")
                                                                ' Fill keys and values
                                                                'bag.PutValue("CanonicalName", resCanonicalName)
                                                                bag.PutValue("cn", rescn)
                                                                bag.PutValue("ObjectGUID", resObjectGUID)
                                                                bag.PutValue("XProxyContext", resXProxyContext)
                                                                bag.PutValue("UID_UNSContainerB", resUID_UNSContainerB)
                                                      End If
                                                      '
                                                      ' Additional values can be put into the bag here
                                                      '
      
                                                      If Not obj Is Nothing Then
                                                                If obj.IsDeleted Then
                                                                          ' Recall deleted object
                                                                          obj.Recall()
                                                                End If
                                                                ' Change object
                                                                bag.ChangeObject(obj, True)
                                                                Dim isUpdate As Boolean = obj.IsLoaded
      
                                                                ' Save it
                                                                If obj.IsDifferent Then
                                                                          obj.Save()
      
                                                                          If isUpdate Then
                                                                                    counter.Increment(LineType.Updated)
                                                                          Else
                                                                                    counter.Increment(LineType.Inserted)
                                                                          End If
                                                                Else
                                                                          counter.Increment(LineType.Unchanged)
                                                                End If
                                                                If elem Is Nothing Then
                                                                          ' Include in our collection to avoid double imports of elements
                                                                          Dim keyInsert As String
                                                                          keyInsert = obj.GetValue("ObjectGUID").String
                                                                          colImport.Insert(obj)
                                                                          Dim elemInsert As IColElem = colImport(colImport.Count - 1)
                                                                          elemsByKey(keyInsert) = elemInsert
                                                                          elemInsert.IsSelected = True
                                                                End If
                                                      End If
                                            Catch ex As Exception
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Error in line {0}: {1}", counter(LineType.Total), ViException.ErrorString(ex))#)                          
                                                      counter.Increment(LineType.Error)
                                                      doNotDelete = True
                                            End Try
                                            If (counter(LineType.Total) Mod 100) = 0 Then
                                                      dfImport.SetProgressInfo(#LD("{0} lines imported, {1} ignored, {2} inserted, {3} updated, {4} errors", counter(LineType.Total), counter(LineType.Unchanged), counter(LineType.Inserted), counter(LineType.Updated), counter(LineType.Error))#)
                                            End If
                                  Next ' ForEach line
      
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
                        '
            ' Superset handling
            '
            If StopProcessing Then
                                  dfImport.Log(MsgSeverity.Warning, #LD("No superset handling, because import was stopped.")#)
                        ElseIf doNotDelete Then
                                  dfImport.Log(MsgSeverity.Warning, #LD("There were errors during the parsing of input data. No elements will be deleted to prevent data loss.")#)
                        Else 
                                  logSection = dfImport.LogSection(#LD("Removing entries missing in the import")#)
                                  Try
                                            Dim toDelete As New List(Of IColElem)()
                                            For Each elem As IColElem In colImport
                                                      Try
                                                                If elem.IsSelected Then
                                                                          Continue For   ' Found in the import
                                                                End If
                                                                toDelete.Add(elem)
                                                      Catch ex As Exception
                                                                dfImport.Log(MsgSeverity.Serious, #LD("Error removing object {0}", elem.Display)#)
                                                                dfImport.LogExecption(ex)
                                                      End Try
                                            Next
                                            For Each elem As IColElem In toDelete
                                                      Dim display As String = Nothing          ' There are no displays in slim collections
                                                      Try
                                                                obj = elem.Create()
                                                                display = obj.Display
                                                                obj.Delete()
                                                                obj.Save()
                                                                VID_Write2Log(logFile,String.Format("DELETE on {0}", elem.GetValue("cn")))
                                                                counter.Increment(LineType.Deleted)
                                                                dfImport.SetProgressInfo(#LD("{0} of {1} deleted", counter(LineType.Deleted), toDelete.Count)#)
                                                      Catch ex As Exception
                                                                If String.IsNullOrEmpty(display) Then
                                                                          display = New DbObjectKey(table, elem).ToXmlString()
                                                                End If
                                                                dfImport.Log(MsgSeverity.Serious, #LD("Error deleting object {0}", display)#)
                                                                dfImport.LogExecption(ex)
      
                                                                counter.Increment(LineType.Error)
                                                      End Try
                                            Next
                                  Finally
                                            If Not logSection Is Nothing Then 
                                                      logSection.Dispose()
                                                      logSection = Nothing
                                            End If
                                  End Try
                        End If
        Finally
            
                        Connection.Variables.Remove("FULLSYNC")
        End Try
              Using dfImport.LogSection(#LD("Resultate")#)
                        dfImport.Log(#LD("{0} lines imported", counter(LineType.Total))#)
                        dfImport.Log(#LD("{0} header lines", counter(LineType.Header))#)
                        dfImport.Log(#LD("{0} inserted", counter(LineType.Inserted))#)
                        dfImport.Log(#LD("{0} changed", counter(LineType.Updated))#)
                        dfImport.Log(#LD("{0} deleted", counter(LineType.Deleted))#)
                        dfImport.Log(#LD("{0} not changed", counter(LineType.Unchanged))#)
                        dfImport.Log(#LD("{0} not found", counter(LineType.NotFound))#)
                        dfImport.Log(#LD("{0} empty lines", counter(LineType.Empty))#)
                        dfImport.Log(#LD("{0} errors", counter(LineType.Error))#)
              End Using
    End Sub

    UNSAcctB in UNS Group B Import

    #If Not SCRIPTDEBUGGER Then
    References VI.DataImport.dll
    Imports System.Collections.Generic
    Imports System.IO
    Imports System.Globalization
    Imports VI.DB.Specialized
    Imports VI.DataImport
    #End If
    Public Sub TST_DBApp2_UNSAccountBInUNSGroupB_Import(ByVal dfImport As DataFileImport)
        ' Standard variables
        Dim logSection As IDisposable
        
        ' Table
        Dim table As TableDef = Connection.Tables("UNSAccountBInUNSGroupB")
              Dim obj As ISingleDbObject = Nothing
              ' Line counter
        Dim counter As New LineCounter()
              ' Do not delete any objects, if this marker is set
              Dim doNotDelete As Boolean = False
              ' Value provider
              Dim lineData As New LineValueProvider(New String() {"Instance", "User ID", "User Name", "Role ID", "Role Name", "Role Updated By", "Role Updated On"})
              Provider = lineData
              ' Import from an external database
              Dim lineProvider As New DbLineProvider()
              lineProvider.ProviderName = "System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
              lineProvider.ConnectionString =  Connection.GetConfigParm("Custom\DBApp2\ConnectionString")
              ' SQL statement
              Dim statement As New PlainSqlStatement()
              statement.Statement = "SELECT 'COI' AS Instance " & _
    "       , U.UserId AS [User ID] " & _
    "       , U.UserName AS [User Name] " & _
    "       , R.RoleTID AS [Role ID] " & _
    "       , R.RoleName AS [Role Name] " & _
    "       , RUX.LastUpdateID [Role Updated By] " & _
    "       , RUX.LastUpdated [Role Updated On] " & _
    "FROM TST..ssMasUser U " & _
    "INNER JOIN TST..ssMasRoleUserXref RUX ON U.UserId = RUX.UserId " & _
    "INNER JOIN TST..ssMasRole R ON RUX.RoleTID= R.RoleTID " & _
    "WHERE R.AutoGenerated = 0" 
      
              lineProvider.Statement = statement
               
              '
        ' Configure column resolution
              '
      
        ' Column indices
        Const iUID_UNSAccountB As Integer = 1
        Const iUID_UNSGroupB As Integer = 3
              ' Resolver to get column data from import data
              ' Build a dictionary: The last parameter forces exceptions when duplicate keys are found
        Dim columnUID_UNSAccountB As New ResolveImportValueHashed( _
            Connection, _
            ObjectWalker.ColDefs(table, "FK(UID_UNSAccountB).AccountName"), False)
              ' Build a dictionary: The last parameter forces exceptions when duplicate keys are found
        Dim columnUID_UNSGroupB As New ResolveImportValueHashed( _
            Connection, _
            ObjectWalker.ColDefs(table, "FK(UID_UNSGroupB).ObjectGUID"), False)
              ' Set connection variables
              Connection.Variables("FULLSYNC") = "true"
        Try
            '
            ' Create a dictionary for the destination table
            '
            Dim colImport As IColDbObject = Connection.CreateCol("UNSAccountBInUNSGroupB")
            Dim elemsByKey As New Dictionary(Of String, IColElem)(StringComparer.OrdinalIgnoreCase)
                        logSection = dfImport.LogSection(#LD("Bilde Index über bestehende Daten")#)
                        Try
                                  ' Set keys and values as display items to load them with the collection
                                  colImport.Prototype("UID_UNSAccountB").IsDisplayItem = True
                                  colImport.Prototype("UID_UNSGroupB").IsDisplayItem = True
                                  colImport.Prototype.WhereClause = "xproxycontext = N'DBApp2'"
      
                                  ' Load the collection
                                  colImport.Load(CollectionLoadType.Slim)
                                  ' Put the entries in the dictionary
                                  For Each elem As IColElem In colImport
                                            Dim key As String
                                            key = elem.GetValue("UID_UNSAccountB").String _
                                                        & Chr(0) & elem.GetValue("UID_UNSGroupB").String _
                                            If elemsByKey.ContainsKey(key) Then
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Duplicate key: {0}", key)#)
                                            Else
                                                      elemsByKey.Add(key, elem)
                                            End If
                                  Next
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
                        logSection = dfImport.LogSection(#LD("Import data from file")#)
            Try
                                  For Each line As Line In lineProvider.GetLines(counter)
                                            ' Pay attention to the Stop flag
                                            If StopProcessing Then
                                                      dfImport.Log(MsgSeverity.Warning, #LD("Abarbeitung wurde gestoppt.")#)
                                                      Exit For
                                            End If 
      
                                            Try
                                                      ' Get raw data from the current line
                                                      Dim key As String
                                                      Dim valUID_UNSAccountB As String
                                                      Dim resUID_UNSAccountB As String
                                                      Dim valUID_UNSGroupB As String
                                                      Dim resUID_UNSGroupB As String
                                                                valUID_UNSAccountB = line.GetValue(iUID_UNSAccountB)
                                                                valUID_UNSGroupB = line.GetValue(iUID_UNSGroupB)
                                                                '
                                                                ' Here is the place to check and change raw data
                                                                '
                                                                lineData.Line = line
      
                                                                Value = valUID_UNSAccountB
      
                                                                ' Start of convert script for UID_UNSAccountB
                                                                ' Value = "DBApp2/Users/" & CStr(Value)
                                                                ' End of convert script for UID_UNSAccountB
      
                                                                valUID_UNSAccountB = DbVal.ConvertTo(Of String)(Value)
      
                                                                Dim f As ISqlFormatter = Connection.SqlFormatter
                                                                ' Convert raw data to final column data values
                                                                'resUID_UNSAccountB = DbVal.ConvertTo(Of String)(columnUID_UNSAccountB.ResolveValue(valUID_UNSAccountB), lineProvider.Culture)
                                                                resUID_UNSAccountB = Connection.GetSingleProperty("UNSAccountB","UID_UNSAccountB", _
                                                                  f.AndRelation(f.Comparison("accountname",valUID_UNSAccountB,ValType.String), _
                                                                          f.Comparison("xproxycontext", "DBApp2", ValType.String)))
                                                                resUID_UNSGroupB = DbVal.ConvertTo(Of String)(columnUID_UNSGroupB.ResolveValue(valUID_UNSGroupB), lineProvider.Culture)
                                                                '
                                                                ' Here is the place to check and change final column data
                                                                '
                                                                '
                                                                ' Here starts the standard handling. It is recommended to not change anything beyond this point.
                                                                '
                                                                '
                                                                ' Create key for this line
                                                                '
                                                                key = DbVal.ConvertTo(Of String)(resUID_UNSAccountB) _
                                                                          &  Chr(0) & DbVal.ConvertTo(Of String)(resUID_UNSGroupB) _
      
                                                      Dim elem As IColElem = Nothing
                                                      Dim bag As New PropertyBag()
                                                      If elemsByKey.TryGetValue(key, elem) Then
                                                                '
                                                                ' Found -> Update
                                                                '
                                                                ' Element in der Collection für die Übermengenbehandlung markieren
                                                                elem.IsSelected = True
                                                                ' Map values
                                                                'Dim found As Boolean = False
                                                                'If found Then
                                                                          ' Create object only when values have changed 
                                      
                                                                '          obj = elem.Create()
                                                                '          If Not obj.IsLoaded Then
                                                                '                    obj.Load()
                                                                '          End If
                                                                'Else
                                                                '          counter.Increment(LineType.Unchanged)
                                                                'End If
                                                      Else
                                                                '
                                                                ' Not found -> Insert
                                                                '
                                                                obj = Connection.CreateSingle("UNSAccountBInUNSGroupB")
                                                                ' Fill keys and values
                                                                bag.PutValue("UID_UNSAccountB", resUID_UNSAccountB)
                                                                bag.PutValue("UID_UNSGroupB", resUID_UNSGroupB)
                                                                bag.PutValue("XProxyContext", "DBApp2")
                                                      End If
                                                      '
                                                      ' Additional values can be put into the bag here
                                                      '
      
                                                      If Not obj Is Nothing Then
                                                                If obj.IsDeleted Then
                                                                          ' Recall deleted object
                                                                          obj.Recall()
                                                                End If
                                                                ' Change object
                                                                bag.ChangeObject(obj, True)
                                                                Dim isUpdate As Boolean = obj.IsLoaded
      
                                                                ' Save it
                                                                If obj.IsDifferent Then
                                                                          obj.Save()
      
                                                                          If isUpdate Then
                                                                                    counter.Increment(LineType.Updated)
                                                                          Else
                                                                                    counter.Increment(LineType.Inserted)
                                                                          End If
                                                                Else
                                                                          counter.Increment(LineType.Unchanged)
                                                                End If
                                                                If elem Is Nothing Then
                                                                          ' Include in our collection to avoid double imports of elements
                                                                          Dim keyInsert As String
                                                                          keyInsert = obj.GetValue("UID_UNSAccountB").String _
                                                                                    & Chr(0) & obj.GetValue("UID_UNSGroupB").String _
                                                                          colImport.Insert(obj)
                                                                          Dim elemInsert As IColElem = colImport(colImport.Count - 1)
                                                                          elemsByKey(keyInsert) = elemInsert
                                                                          elemInsert.IsSelected = True
                                                                End If
                                                      End If
                                            Catch ex As Exception
                                                      dfImport.Log(MsgSeverity.Serious, #LD("Error in line {0}: {1}", counter(LineType.Total), ViException.ErrorString(ex))#)
                              doNotDelete = True
                                                      counter.Increment(LineType.Error)
                                            End Try
                                            If (counter(LineType.Total) Mod 100) = 0 Then
                                                      dfImport.SetProgressInfo(#LD("{0} lines imported, {1} ignored, {2} inserted, {3} updated, {4} errors", counter(LineType.Total), counter(LineType.Unchanged), counter(LineType.Inserted), counter(LineType.Updated), counter(LineType.Error))#)
                                            End If
                                  Next ' ForEach line
      
            Finally
                If Not logSection Is Nothing Then 
                                            logSection.Dispose()
                                            logSection = Nothing
                                  End If
            End Try
                        '
                        ' Superset handling
            '
            If StopProcessing Then
                                  dfImport.Log(MsgSeverity.Warning, #LD("No superset handling, because import was stopped.")#)
                        ElseIf doNotDelete Then
                                  dfImport.Log(MsgSeverity.Warning, #LD("There were errors during the parsing of input data. No elements will be deleted to prevent data loss.")#)
                        Else 
                                  logSection = dfImport.LogSection(#LD("Removing entries missing in the import")#)
                                  Try
                                            Dim toDelete As New List(Of IColElem)()
                                            For Each elem As IColElem In colImport
                                                      Try
                                                                If elem.IsSelected Then
                                                                          Continue For   ' Found in the import
                                                                End If
                                                                toDelete.Add(elem)
                                                      Catch ex As Exception
                                                                dfImport.Log(MsgSeverity.Serious, #LD("Error removing object {0}", elem.Display)#)
                                                                dfImport.LogExecption(ex)
                                                      End Try
                                            Next
                                            For Each elem As IColElem In toDelete
                                                      Dim display As String = Nothing          ' There are no displays in slim collections
                                                      Try
                                                                obj = elem.Create()
                                                                display = obj.Display
                                                                obj.Delete()
                                                                obj.Save()
                                                                counter.Increment(LineType.Deleted)
                                                                dfImport.SetProgressInfo(#LD("{0} of {1} deleted", counter(LineType.Deleted), toDelete.Count)#)
                                                      Catch ex As Exception
                                                                If String.IsNullOrEmpty(display) Then
                                                                          display = New DbObjectKey(table, elem).ToXmlString()
                                                                End If
                                                                dfImport.Log(MsgSeverity.Serious, #LD("Error deleting object {0}", display)#)
                                                                dfImport.LogExecption(ex)
      
                                                                counter.Increment(LineType.Error)
                                                      End Try
                                            Next
                                  Finally
                                            If Not logSection Is Nothing Then 
                                                      logSection.Dispose()
                                                      logSection = Nothing
                                            End If
                                  End Try
                        End If
      
        Finally
            
                        Connection.Variables.Remove("FULLSYNC")
        End Try
              Using dfImport.LogSection(#LD("Resultate")#)
                        dfImport.Log(#LD("{0} lines imported", counter(LineType.Total))#)
                        dfImport.Log(#LD("{0} header lines", counter(LineType.Header))#)
                        dfImport.Log(#LD("{0} inserted", counter(LineType.Inserted))#)
                        dfImport.Log(#LD("{0} changed", counter(LineType.Updated))#)
                        dfImport.Log(#LD("{0} deleted", counter(LineType.Deleted))#)
                        dfImport.Log(#LD("{0} not changed", counter(LineType.Unchanged))#)
                        dfImport.Log(#LD("{0} not found", counter(LineType.NotFound))#)
                        dfImport.Log(#LD("{0} empty lines", counter(LineType.Empty))#)
                        dfImport.Log(#LD("{0} errors", counter(LineType.Error))#)
              End Using
    End Sub

    Anything else look unusual?

  • 0 over 10 years ago

    ok, Dave.

    You've checked the mentioned job parameter - it has the correct value "VID_GetValueofDialogDatabases("ConnectionString")".

    But I'm not sure:

    Did you checked the content of the field "DialogDatabase.ConnectionString" too?

    Does it contain the connectionstring to the correct db?

    Steffen

  • 0 over 10 years ago

    Yes Steffen, it does have the correct string. I just triple checked again and the field in the DialogDatabase.ConnectionString MATCHES the correct Database.

  • 0 over 10 years ago

    Hello Dave,

    I'm thinking and thinking ...

    The scripts are looking correctly.

    Sorry for the dumb question:

    Are you sure that nothing was imported in the db?

    You should look via SQL or the ObjectBrowser to see all objects without a filter.

    You told us that neither UNSAccountB's nor UNSGroupB's were created.

    What about the containers?

    Same issue?

    In the end of every import you should get in the log file the output of the counters:

    dfImport.Log(#LD("{0} lines imported", counter(LineType.Total))#)

    dfImport.Log(#LD("{0} header lines", counter(LineType.Header))#)

    dfImport.Log(#LD("{0} inserted", counter(LineType.Inserted))#)

    dfImport.Log(#LD("{0} changed", counter(LineType.Updated))#)

    dfImport.Log(#LD("{0} deleted", counter(LineType.Deleted))#)

    dfImport.Log(#LD("{0} not changed", counter(LineType.Unchanged))#)

    dfImport.Log(#LD("{0} not found", counter(LineType.NotFound))#)

    dfImport.Log(#LD("{0} empty lines", counter(LineType.Empty))#)

    dfImport.Log(#LD("{0} errors", counter(LineType.Error))#)

    What's the content?

    If all these tests/questions will not bring the light in the darkness I suggest to activate the SQL log and the Object log for the import to see what happens on the object layer and the SQL layer during the import.

    Steffen

  • 0 over 10 years ago

    Okay, I opened the database with SQL Management Studio and checked the various tables. UNSContainerB DOES have data but the UNSGroupB and UNSAccountB does not. Just for good measure, I used document comparison tools to verify that the code for the import scripts is identical in PRD and DEV and it is. Every file used for import is identical. So it can't be a code issue.

  • 0 over 10 years ago

    Hi Dave

    it took me a while to get through your script code, but i think i got it:

    This is the code you're using in the UNSAccountB as well as in the UNSGroupB script to set the UID_UNSContainerB value for the object:

    valUID_UNSContainerB = "DBApp2/Users"

    valUID_UNSContainerB = "DBApp2/Groups"

    As long as you did not freak up the UID of the affected UNSContainerB-objects, i'm pretty sure that this UID is incorrect.

    Correct?

    Cheers

    Carsten