Unique Email Address is not getting generated when data is imported via CSV file

Is the template marked as overwriting?

How do you import the data?

What is your script CCC_Build_DefaultEmailAddress doing?

no template is not marked "overwrites". I am using CSV connector to import the data and the script should generate unique email address for the users but it is generating same email address for all the users in csv having same first name and last name.

Next question, is the CSV workflow step marked as data import? (it has to)

But even then, you need to mark the template as overwriting.

Data Import is marked in sync workflow and now I marked the template as overwriting still it is not generating unique email address 

Then you need to post the code of your script CCC_Build_DefaultEmailAddress because the error probably is located here.

We have tested this script in other environment having same One Identity Managaer version and it is working there. Only difference in these two environments is central account is also getting generated via script in other environment where in in this environment we are just getting central account via csv. Below is the script 

#If Not SCRIPTDEBUGGER Then
Imports System.Collections.Generic
Imports System.Data
#End If

Public Function CCC_Build_DefaultEmailAddress(ByVal Firstname1 As String, ByVal Lastname1 As String, ByVal uidperson As String, ByVal geography As String) As String

Dim m As Int32 = 0
Dim personAccnt As String = ""
Dim account As String
Dim prefix As String
Dim maildom As String = String.Empty
CCC_Build_DefaultEmailAddress = String.Empty
Dim defaultemailaddress As String
Dim llength As Int32
Dim lalength As Int32
Dim difference As Int32
Dim difference1 As Int32
Dim Firstname As String
Dim Lastname As String
Dim reader As SqlDataReader
Dim con As New SqlConnection
Dim f As ISQLFormatter = Connection.SqlFormatter
Firstname = VID_FormatAndRemoveSpecialCharacters(Firstname1)
Lastname = VID_FormatAndRemoveSpecialCharacters(Lastname1)
If Firstname.Length > 0 And Lastname.Length > 0 Then
account = Firstname & "." & Lastname
End If

account = VI_AE_FormatConvertUmlaut_Sonderzeichen(account)
prefix = account

If geography = "CA" Then
maildom = "abc.ca"
Else
maildom = Connection.GetConfigParm("QER\Person\DefaultMailDomain")
End If
If maildom = "" Then
Exit Function
Else
If Not maildom.StartsWith("@") Then
maildom = "@" & maildom
End If
End If

' fill existing addresses In a dictionary


CCC_Random_TimeDelay()

Dim ConnectionString As String = Connection.GetSingleProperty("DialogDatabase", "ConnectionString","")
con.ConnectionString = ConnectionString


Dim Query As String= "select DefaultEmailAddress from Person where DefaultEmailAddress like 'FSub%.LSub%DSub'"
Dim existing As New List(Of String)
Query = Query.Replace("FSub", Firstname.Substring(0,1))
Query = Query.Replace("LSub", Lastname.Substring(0,1))
Query = Query.Replace("DSub", maildom)

Dim cmd As New SqlCommand(Query, con)

con.Open()

reader = cmd.ExecuteReader()


While reader.Read()
existing.Add(reader(0).ToString)

End While

con.close()
defaultemailaddress = account & maildom
If account.Length < 64 Then
If Not existing.Contains(defaultemailaddress, StringComparer.InvariantCultureIgnoreCase) Then
Return defaultemailaddress
End If
End If

While(m < 100)

While(m < 10)
If account.Length < 64 Then


If Not existing.Contains(defaultemailaddress, StringComparer.InvariantCultureIgnoreCase) Then
Return defaultemailaddress
End If
account = Firstname & "." & Lastname & "0" & m
defaultemailaddress = account & maildom
Else
llength = Lastname.Length
difference = account.Length - 64
Lastname = Lastname.Substring(0,(llength - difference - 2))
account = Firstname & "." & Lastname & "0" & m
defaultemailaddress = account & maildom
End If
m = m + 1


End While
While(m > 9)

If account.Length < 64 Then
If Not existing.Contains(defaultemailaddress, StringComparer.InvariantCultureIgnoreCase) Then
Return defaultemailaddress
End If
account = Firstname & "." & Lastname & m
defaultemailaddress = account & maildom

Else
lalength = Lastname.Length
difference1 = account.Length - 64
Lastname = Lastname.Substring(0,(lalength - difference1 - 2))
account = Firstname & "." & Lastname & "0" & m
defaultemailaddress = account & maildom
End If
m = m + 1
End While

End While
End Function

Honestly, I do not know where to start. I think I already advised strongly against the usage of direct SQL commands against the OneIM database and here we are again.

The complete code passage around fetching a connection string from the database and using an SQL Reader to fetch something from the database is something that is against any coding practice for OneIM and is something that will stop to work in the moment you are encrypting the database (which should be the case in ANY production environment). In addition, this would stop you or your customer from using the application server as a connection method.

Secondly, you already identified that your template works (somewhat) if you have a template on Centralaccount as well. You have to keep in mind, that the templates are not only executed once but multiple times. The times depend on the number of executions triggered by data changes on notifying columns ($columnname generates a notification trigger) and of course the code you are providing.

My advice is to take a look at the default template for Person.DefaultEmailaddress and the script VI_AE_CreatedefaultMailAddress and use them as a base to implement your modifications.