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

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!

Parents

0 dshieldschk 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

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

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

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

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

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