I am involved in a Data Migration project where we are moving data from SQL Table(s) source into Dynamics CRM.
We are currently using Kingswaysoft toolkit within SSIS packages to migrate the data using the Dynamics CRM connectors, but this requires us to hand build separate pipelines for each of the Dynamics entities we wish to migrate data into.
I would like to use ADF and reusable pipelines to achieve this ideally. I have been building a POC reusable pipeline to copy the data between SQL tables & Dynamics CRM entities but I've hit a bit of a stumbling block with regards to the functionality of using Copy Activity to migrate the data.
The issue I have is with regards to how I can robustly handle records that fail to load into Dynamics for one reason or another (eg they violate key, invalid datatype/lengths etc) Obviously this is fairly basic requirement for any data migration.
I have logging enabled on the Copy Activity which logs failed rows into randomly generated logfiles, but its not terribly usable as the entire failed record is shoved in a single field with a strange number of quotes surrounding it and no way of identifying attribute names. This is pretty useless to be able to do anything with from a pipeline point of view.
What we'd like to do, is similar to what we do currently with KWS, off the back of the copy task we can we can simply update the records in the SQL table as having been loaded successfully or errored. This simply allows us to restage the data (fixing bad data at source where possible) and re run the pipeline which will only look for records which haven't yet been successfully loaded or have changed since the previous successful load.
This is a fairly simple requirement but I can't fathom a way to do this within ADF.
So I guess the question is if you have 1 million records in a table, 900k load successfully, 100k fail, how can you design the pipeline to mark those 100k records as having failed (or conversely mark the 900k that were successful as such).
Copy Task has a redirectRowPath output value which i thought used to log failed records to that path (keeping the data structure with attribute names) but it appears that's no longer in use? well not for DynamicsCRM sink anyway.