Excellent guidance from everyone. We successfully completed the bulk import after following these steps:
1. Azure AD Connector Configuration:
First, we reviewed and corrected our connector configuration. The key settings that needed adjustment:
Connector Config File (azure-ad-connector.properties):
azure.ad.sync.batch.size=100
azure.ad.sync.unique.identifier=userPrincipalName
azure.ad.sync.retry.on.conflict=false
azure.ad.sync.transaction.timeout=300
The critical change was reducing batch size from 200 to 100 users. This prevents transaction timeouts and makes it easier to identify problematic users if a batch fails. We also disabled automatic retry on conflicts to avoid creating more duplicate records.
2. User Principal Name Uniqueness Check:
We ran the SQL query Mike suggested and found 23 duplicate UPNs:
SELECT UserPrincipalName, COUNT(*) as DuplicateCount,
STRING_AGG(CAST(UserId AS VARCHAR), ',') as UserIds
FROM LaborUser
GROUP BY UserPrincipalName
HAVING COUNT(*) > 1;
This showed us exactly which users had duplicates and their internal IDs. Most duplicates had null values for EmployeeNumber, Department, or ShiftGroup - clear indicators they were incomplete records from failed sync attempts.
3. Database Cleanup Process:
We used the Labor Management API to safely remove duplicate records rather than direct database deletes:
Step 1 - Identify incomplete records:
For each duplicate UPN, we called the API to get full user details:
GET /api/v1/labor/users?upn={userPrincipalName}
This returned all records with that UPN. Incomplete records were missing required fields like EmployeeNumber or had null Department values.
Step 2 - Deactivate duplicates:
For each incomplete user ID, we deactivated first:
PATCH /api/v1/labor/users/{userId}
{
"status": "INACTIVE",
"reason": "Duplicate record cleanup"
}
Step 3 - Remove duplicates:
After deactivation, we deleted the incomplete records:
DELETE /api/v1/labor/users/{userId}?force=false
Using force=false ensures the API validates there are no dependent records (shift assignments, attendance logs) before deletion. If dependencies exist, the API returns a 409 conflict and you need to handle those manually.
We cleaned up all 23 duplicate UPNs using this process, which took about 2 hours with proper validation at each step.
4. Pre-Import Validation:
Before running the bulk import again, we added a validation step:
Check Azure AD users against existing LaborUser table:
SELECT azad.UserPrincipalName
FROM AzureADSyncStaging azad
INNER JOIN LaborUser lu ON azad.UserPrincipalName = lu.UserPrincipalName
WHERE lu.Status = 'ACTIVE';
This query (using the staging table the connector populates before sync) identified 12 users who already existed as active records. We excluded these from the bulk import to avoid conflicts.
5. Successful Bulk Import:
With clean database and optimized connector config, the bulk import completed successfully:
- Total users imported: 438 (450 minus 12 existing active users)
- Batches processed: 5 batches of 100 users, 1 batch of 38 users
- Duration: 18 minutes
- Errors: 0
Post-Import Verification:
We validated the import by checking:
- All users have unique UPNs in LaborUser table
- EmployeeNumber, Department, and ShiftGroup populated for all users
- User status is ACTIVE for all imported records
- Shift scheduling can now assign imported supervisors and operators
Key Lessons:
- Always clean up failed import attempts before retrying
- Use the Labor Management API for cleanup, never direct database deletes
- Reduce batch size for large imports to prevent transaction timeouts
- Validate Azure AD users against existing records before bulk import
- The Azure AD connector doesn’t automatically handle cleanup of partial imports - you must do this manually
Our shift scheduling is now fully operational with all 450 users properly imported and assigned to their respective departments and shift groups.