Like with KingswaySoft, Azure Data Factory (ADF) also has the ability to create a SQL Server table when copying data from Dynamics 365 (D365/CRM/Dataverse).
However, KingswaySoft does a better job at properly creating the SQL table based on the source D365 table's columns. It reads the table metadata and creates the SQL table based on that information. ADF, though, is not as reliable in this regard. It samples the D365 table data to determine which SQL columns to create and the data type for each column.
One problem with ADF's approach is, if you want to specify a FetchXml query for the Copy Activity, you will likely end up with a SQL table that has fewer columns as those specified in your FetchXml statement. Again, this might occur if ADF does not find any data in one or more of the specified D365 table columns. It won't know what SQL columns to create since there's no data to sample.
If you still want to use ADF to copy D365 data to SQL Server using a FetchXml statement as the source, then here's one work-around to force ADF to create all SQL columns.
1) In the ADF pipeline's Copy Activity, click Source (your source is D365) and set the Use Query option to Table.
2) On the Mapping page, click Import Schemas. This will list column mappings for all columns for the D365 table (entity).
3) Back on the Source page, change the Use Query option to Query and specify the FetchXml. Use <all-attributes /> to assure that you will get all D365 column data. This is where you can specify filter conditions for your FetchXml statement.
4) On the Sink page, make sure the Auto Create Table option is selected.
5) Run the pipeline to copy the D365 data to Azure SQL.
You'll also discover another problem with ADF in this process: It creates D365 string columns as nvarchar(max) in SQL. This can be troublesome if you want to apply an index on one or more of those columns.
In conclusion, our suggestion is to use KingswaySoft as a more reliable and overall better way to copy data from Dynamics 365, if the intention is to auto-create a target SQL table and you want all columns created. ADF can also get the job done, it's just not as ideal for this purpose.
Comentarios