Lost lead zeros when importing Orgdata

Software product name	PerformanceDNA
Version Number	8.5.x and Below
Subject	Lost lead zeros when importing Orgdata

Symptoms

You are using the Orgdata process to import user data and build a reporting hierarchy and find that data with a leading 0 (zero) is missing the 0(s). When checking the Orgdata process the data is correct in the Staging table however it is incorrect in the Orgdata table and Users data within the product.

Resolution

The issue is caused by SQL server converting each value in to the most suitable data type based on the value’s content. This results in numbers with leading 0s being treated as a number and the leading 0s are stripped meaning 000023 becomes 23. There are 2 possible solutions to overcome this issue:

• Add a post import SQL script to the Orgdata XML config to manually copy the data from Staging to Orgdata referencing all of the columns. The resulting Import Sources configuration would look similar to the following example:

<ImportSources CsvFilePath="C:\Genesys\Applications\OrgData\CurrentData"
BackupFilePath="C:\Genesys\Applications\OrgData\BackedUpData"
PostImportSql="TRUNCATE Orgdata INSERT INTO Orgdata (Id, EmployeeID, FirstName, LastName, PositionID, PositionName, LMPositionID, CacheData, WfmUserId, PortalEmplId,                                                             PortalRole, Site,TeamManagerID) Select Id, EmployeeID, FirstName, LastName,                                                             PositionID, PositionName, LMPositionID, CacheData, WfmUserId, PortalEmplId,                                                             PortalRole, Site,TeamManagerID 
FROM Staging">

• Alternatively, similar SQL or an UPDATE query can be added to the end of the existing Post Import SQL or Stored Procedure to repopulate the data with the correct formatting.