Compliance Import Error The locale id '1039' of the source column 'FileName' and the locale id '1033' of the destination column 'FileName' do not match

[#1]

Summary

Newly added SCCM adapter added and running a Compliance Import generates an error.

Synopsis

Newly added SCCM adapter added and running a Compliance Import generates an error:

----------------------------
The locale id '1039' of the source column 'FileName' and the locale id '1033' of the destination column 'FileName' do not match.
----------------------------

Reference incident: SIOC-000152641

Workaround

Fixes were made by modifying the SMS reader and casting the fields causing the error

(6 fields in total neeed casting to the source collation.).

Changes made to the FileEvidence.xml in SMS reader

<!--Get the file names from Compliance Manager file evidence and transfer to the source database-->
<!--Because we don't perform a real LIKE match, escape all of the LIKE match characters except %-->
<Reader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xsi:type="TargetToSource"
Name="InsertIntoTempApplicationFile"
Order="402"
Retries="1"
Table="#ApplicationFile">
<![CDATA[
DECLARE @EscapeChar nchar(1)
SET @EscapeChar = '\'

SELECT
CASE WHEN CHARINDEX('%', fe.FileName) <> 0 THEN 1 ELSE 0 END AS HasWildcards,
FileName = CAST(REPLACE(REPLACE(REPLACE(REPLACE(fe.FileName, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']') as Nvarchar(256)) COLLATE SQL_Latin1_General_CP1_CI_AS,
FileVersion = CAST(REPLACE(REPLACE(REPLACE(REPLACE(fe.FileVersion, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']') as Nvarchar(100)) COLLATE SQL_Latin1_General_CP1_CI_AS,
Company = CAST(REPLACE(REPLACE(REPLACE(REPLACE(fe.Company, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']') as Nvarchar(100)) COLLATE SQL_Latin1_General_CP1_CI_AS,
Description = CAST(REPLACE(REPLACE(REPLACE(REPLACE(fe.Description, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']') as Nvarchar(200)) COLLATE SQL_Latin1_General_CP1_CI_AS

--FileName = REPLACE(REPLACE(REPLACE(REPLACE(fe.FileName, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
--FileVersion = REPLACE(REPLACE(REPLACE(REPLACE(fe.FileVersion, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
--Company = REPLACE(REPLACE(REPLACE(REPLACE(fe.Company, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']'),
--Description = REPLACE(REPLACE(REPLACE(REPLACE(fe.Description, @EscapeChar, @EscapeChar + @EscapeChar), '_', @EscapeChar + '_'), '[', @EscapeChar + '['), ']', @EscapeChar + ']')
FROM dbo.FileEvidence AS fe
WHERE fe.FileEvidenceID IN (SELECT FileEvidenceID FROM dbo.SoftwareTitleFileEvidence AS stfe)
]]>
</Reader>

Changes made to the WMIEvidence.xml i SMS reader:

<!--Transfer the WMI names used in the Compliance Manager ARL to the #ApplicationWMI temporary table on the source
database.-->
<Reader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xsi:type="TargetToSource"
Name="InsertIntoWMITempApplicationFile"
Order="604"
Retries="1"
Table="#ApplicationWMI">
<![CDATA[
SELECT distinct
CAST(we.ClassName as Nvarchar(50)) COLLATE SQL_Latin1_General_CP1_CI_AS,
CAST(we.PropertyName as Nvarchar(50)) COLLATE SQL_Latin1_General_CP1_CI_AS
--we.ClassName,
--we.PropertyName
FROM dbo.WMIEvidence as we
JOIN dbo.SoftwareTitleWMIEvidence as stwe
ON we.WMIEvidenceID = stwe.WMIEvidenceID

]]>
</Reader>