Export filter doesn't work

[AlexeiPoplutin]

Description I'm trying to export data from several tables and for one of the tables I added a Fetch XML filter. If the flag "OnlyActiveRecords" is set to True, then export process ignores my filter and exports all the active records from all the tables that are described in the schema file. If I set that flag to false, the tool fails when tries to export data for the filtered entity. Log:

23-Jun-2022 00:01:57 - Verbose:CrmFileDataExporter GetProcessors started 23-Jun-2022 00:01:57 - Verbose:CrmFileDataImporter GetProcessors finished 23-Jun-2022 00:01:57 - Info:GenericDataMigrator MigrateData started 23-Jun-2022 00:01:57 - Info:GenericDataMigrator PerformMigratePass started, passNo:1 23-Jun-2022 00:01:57 - Info:DataFileStoreWriter Reset performed 23-Jun-2022 00:01:57 - Verbose:DataCrmStoreReader Reset performed 23-Jun-2022 00:01:57 - Verbose:DataCrmStoreReader ReadBatchDataFromStore started, queryIndex:0, page0 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader retrieved entity:msdyn_cannedmessage, page:1, query:0, retrievedCount:10, totalEntityCount:10 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader ReadBatchDataFromStore finished, queryIndex:1, page:0, totalCount:10 23-Jun-2022 00:01:58 - Info:GenericDataMigrator PerformMigratePass retrieved data, batchNo:1 entities:10 FirstEntity:msdyn_cannedmessage 23-Jun-2022 00:01:58 - Verbose:DataFileStoreWriter SaveBatchDataToStore started, records:10, batchNo:1 23-Jun-2022 00:01:58 - Verbose:DataFileStoreWriter SaveBatchDataToStore finished 23-Jun-2022 00:01:58 - Info:GenericDataMigrator PerformMigratePass saved data, batchNo:1 entities:10 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader ReadBatchDataFromStore started, queryIndex:1, page0 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader retrieved entity:msdyn_msdyn_cannedmessage_liveworkstream, page:1, query:1, retrievedCount:10, totalEntityCount:10 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader ReadBatchDataFromStore finished, queryIndex:2, page:0, totalCount:20 23-Jun-2022 00:01:58 - Info:GenericDataMigrator PerformMigratePass retrieved data, batchNo:2 entities:10 msdyn_msdyn_cannedmessage_liveworkstream 23-Jun-2022 00:01:58 - Verbose:DataFileStoreWriter SaveBatchDataToStore started, records:10, batchNo:2 23-Jun-2022 00:01:58 - Verbose:DataFileStoreWriter SaveBatchDataToStore finished 23-Jun-2022 00:01:58 - Info:GenericDataMigrator PerformMigratePass saved data, batchNo:2 entities:10 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader ReadBatchDataFromStore started, queryIndex:2, page0 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader retrieved entity:msdyn_msdyn_cannedmessage_msdyn_octag, page:1, query:2, retrievedCount:4, totalEntityCount:4 23-Jun-2022 00:01:58 - Verbose:DataCrmStoreReader ReadBatchDataFromStore finished, queryIndex:3, page:0, totalCount:24 23-Jun-2022 00:01:58 - Info:GenericDataMigrator PerformMigratePass retrieved data, batchNo:3 entities:4 msdyn_msdyn_cannedmessage_msdyn_octag 23-Jun-2022 00:01:58 - Verbose:DataFileStoreWriter SaveBatchDataToStore started, records:4, batchNo:3 23-Jun-2022 00:01:59 - Verbose:DataFileStoreWriter SaveBatchDataToStore finished 23-Jun-2022 00:01:59 - Info:GenericDataMigrator PerformMigratePass saved data, batchNo:3 entities:4 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader ReadBatchDataFromStore started, queryIndex:3, page0 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader retrieved entity:msdyn_liveworkstream, page:1, query:3, retrievedCount:10, totalEntityCount:10 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader ReadBatchDataFromStore finished, queryIndex:4, page:0, totalCount:34 23-Jun-2022 00:01:59 - Info:GenericDataMigrator PerformMigratePass retrieved data, batchNo:4 entities:10 msdyn_liveworkstream 23-Jun-2022 00:01:59 - Verbose:DataFileStoreWriter SaveBatchDataToStore started, records:10, batchNo:4 23-Jun-2022 00:01:59 - Verbose:DataFileStoreWriter SaveBatchDataToStore finished 23-Jun-2022 00:01:59 - Info:GenericDataMigrator PerformMigratePass saved data, batchNo:4 entities:10 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader ReadBatchDataFromStore started, queryIndex:4, page0 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader retrieved entity:msdyn_octag, page:1, query:4, retrievedCount:3, totalEntityCount:3 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader ReadBatchDataFromStore finished, queryIndex:5, page:0, totalCount:37 23-Jun-2022 00:01:59 - Info:GenericDataMigrator PerformMigratePass retrieved data, batchNo:5 entities:3 msdyn_octag 23-Jun-2022 00:01:59 - Verbose:DataFileStoreWriter SaveBatchDataToStore started, records:3, batchNo:5 23-Jun-2022 00:01:59 - Verbose:DataFileStoreWriter SaveBatchDataToStore finished 23-Jun-2022 00:01:59 - Info:GenericDataMigrator PerformMigratePass saved data, batchNo:5 entities:3 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader ReadBatchDataFromStore started, queryIndex:5, page0 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader retrieved entity:msdyn_livechatconfig, page:1, query:5, retrievedCount:2, totalEntityCount:2 23-Jun-2022 00:01:59 - Verbose:DataCrmStoreReader ReadBatchDataFromStore finished, queryIndex:6, page:0, totalCount:39 23-Jun-2022 00:01:59 - Info:GenericDataMigrator PerformMigratePass retrieved data, batchNo:6 entities:2 msdyn_livechatconfig 23-Jun-2022 00:01:59 - Verbose:DataFileStoreWriter SaveBatchDataToStore started, records:2, batchNo:6 23-Jun-2022 00:02:00 - Verbose:DataFileStoreWriter SaveBatchDataToStore finished 23-Jun-2022 00:02:00 - Info:GenericDataMigrator PerformMigratePass saved data, batchNo:6 entities:2 23-Jun-2022 00:02:00 - Verbose:DataCrmStoreReader ReadBatchDataFromStore started, queryIndex:6, page0 23-Jun-2022 00:02:54 - Error: Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 156

Fetch XML is tested in the builder and works fine.

ExportConfig:

{ "ExcludedFields": [], "CrmMigrationToolSchemaPaths": [ "D:\xx\ConfigMigration\schema.xml" ], "CrmMigrationToolSchemaFilters": { "msdyn_oclocalizationdata": "<fetch version=\"1.0\" output-format=\"xml-platform\" mapping=\"logical\" distinct=\"false\">\n <entity name=\"msdyn_oclocalizationdata\">\n <attribute name=\"msdyn_localizedtext\" />\n <attribute name=\"msdyn_customerlanguageid\" />\n <attribute name=\"statecode\" />\n <attribute name=\"msdyn_oclocalizationdataid\" />\n <order attribute=\"msdyn_localizedtext\" descending=\"false\" />\n \n <condition attribute=\"msdyn_oclocalizationdataid\" operator=\"in\" uitype=\"msdyn_oclocalizationdata\">\n 44ff8f8e-5ded-ec11-bb3d-000d3af4d379\n a7527efe-73ed-ec11-bb3d-000d3af4d379\n \n \n \n" }, "PageSize": 1000, "BatchSize": 1000, "TopCount": 10000, "OnlyActiveRecords": true, "JsonFolderPath": "D:\xx\ConfigMigration\ExtractedData", "OneEntityPerBatch": true, "FilePrefix": "ExportedData", "SeperateFilesPerEntity": true, "FieldsToObfuscate": [], "LookupMapping": {} }

Please correct me, if I'm doing something wrong. Thank you.

[tdashworth]

@AlexeiPoplutin Thanks for raising this issue - I agree that does seem confusing. I will ask someone to replicate the problem and investigate.

[AlexeiPoplutin]

I was able to resolve the SQL error issue. Only the tag is needed, now I see that it was mentioned in the pop-up for the filter, but it is worse to put it with a sample in the documentation. But Active/Inactive issues is still in place, so I would keep it open.

And still can't figure out how the GUID mapping works, I would really appreciate it if you point me to the sample or to the documentation. Tried different combinations and couldn't make it work.

[tdashworth]

Nice one for solving the SQL error! I'm not sure where you are referring to regarding the "sample in the documentation".

We understand the confusion with the entity status and are discussing the impacts of changing this logic.

GUID mappings work by searching the Source GUID in each record of the selected type and replacing it with the Target GUID. For example, you have a default Contact to act as "Anonymous" (dev: 00000000-0000-0000-0000-000000000001, prod: 00000000-0000-0000-0000-000000000002). If you were importing Account records where a Contact/Customer lookup was set to Anonymous (00000000-0000-0000-0000-000000000001), during the migration it would be replaced with 00000000-0000-0000-0000-000000000002.

While these are Contact GUIDs, this config sits on the entity where you would find the reference i.e. Accounts.

I hope that's clear 😄

[AlexeiPoplutin]

Thanks for the clarification! I was not sure on which entity the mapping should sit. On the lookup (Contact in your sample) or on the entity itself. Now I got it.

By the sample in the documentation I mean to add a screenshot like this one to the existing ones in Readme.md file, to avoid confusion. Maybe I'm not the only one who tried to insert the whole Fetch query, instead of using just a filter piece of it :)

BTW I noticed another minor issue related to the state. If the toggle is set to export only active records, the export fails for Business Unit, because no such field in that entity :)

Thanks again for your help and clarifications!

[tdashworth]

I'm glad that helped 😄

Looking into the code we have for this logic, you are right - if activeRecordsOnly then generate some fetch for that, otherwise, if filters are provided, then use that. activeRecordsOnly applies to all entities and there is no exception logic for this currently.

[tdashworth]

@AlexeiPoplutin This should now be fixed (in this engine). Please expect an update to the XrmToolbox plugin today. 😄

[AlexeiPoplutin]

@tdashworth thanks a lot guys for developing the tool! Did you fix the issue from my last comment that "only active" toggle caused failure for some entities or the initial question that when we have active only toggle On, it ignores custom filters? :) Just curious :) Thanks again.

[tdashworth]

Both! It will now only apply the active filter when there is no custom filter and the field exists on the entity.