Data missing from Qlik to Azure MSSQL Server

[Sudarsan_K14]

HI,

I am using Qlik Enterpraise manager task for data flow starting DB2 Mainframe(source endpoint) to till Azure sqlserver(target endpoint) .

For that initially,i ran qlik Fullload task today and all 11,358 records from DB2 to Qlik are flown.

But when i check in target database(Azure SQLServer) observed one record is missing.(11,357 records existed)

when i do data comparision observed that ,second record from the below list is missing.

INSURER_ID

CLU_CODE

DESCRIPTION

WHO_UPDATED

WHEN_UPDATED

CLU_CATEGORY

MOT	3v	CLASS THREE (VINTAGE RISKS).	UG03T28	2013-10-15-14.26.30.833490	007
MOT	3V	BUSINESS USE INCLUDING THE CARRIAGE OF GOODS FOR HIRE AND REWARD	TXLMID	2018-05-01-13.12.04.853670	007

These 2 records have same primary key combination (INSUER_ID and CLU_CODE).

But both are jus differed by captial 'V' and small 'v'.

SO i would like to know ,is this issue occuring while data flowing from Qlik to Azure.

 If qlik is filtering out this considering as duplicate record, please let me know the settings in Qlik UI to fix this.

Thanks,

Sudarsan K

07354 816 836

[Dana_Baldwin]

Hi @Sudarsan_K14 

Please check case sensitivity settings in your source & target. Seems the source enforces case sensitivity but the target does not. By default on prem SQL Server does not enforce case sensitivity. Most likely this is caused by the collation setting on the target database.

Thanks,

Dana

If one of the replies helped to resolve the issue, please mark it as a solution & like it to make it easier for others to find.
Thanks in advance!

[Sudarsan_K14]

HI Dana,

Thank you for the inputs.

Out of total 87 tables in Azure sql database ,we found this collation issue for only one record in the table ' UKMITB14_CL_USE ' .

If we change collation settings to Incase sensitive to case sensitive at Database level, will it impact other remaining 86 tables in any manner or causing problem to other tables settings.As this is in UAT, I am not sure  what effect and how many records will be affected due to collation.

Please clarify on this.

Thanks,

Sudarsan K

07354 816 836

[Dana_Baldwin]

Hi @Sudarsan_K14 

It depends on the PK/UI column values, if they have mixed case or not. Generally, if you want the target table data to match the source, it makes sense to ensure the case sensitivity is enforced the same on the target as on the source.

Thanks,

Dana

[funsuk61]

Unfortunately the only way I have come across for integrating Key Vault as a password manager in Qlik is through individual custom codes set up in Qlik Replicator. It should be doable but I was hoping that someone's done something more scalable as my current team has 40 people itself add to that about 25 external contractors + any future developers needing to get at that EDW for different or similar solutions.