Missing and duplicate columns when fetching data from DynamicsCRM i azure synapse copy activity

Catarina Leiria 20 Reputation points
2024-09-02T15:15:44.0533333+00:00

Hi.

When fetching data from Dynamics CRM and copying it to a DW, one of the columns appears in the preview data in the source of the copy activity, but it is not created in the DW ("LeadId"). Moreover, it doe not appear when importing schema in mapping. Additionally, one of the columns is duplicated, creating the originial column and its alias. ("new_localdeprestacaoid" and "LocalPrestacaoId") What could be a possible solution? Thank you!

Part of the fetch: <fetch>

  <entity name="new_localdeprestacao">

    <attribute name="new_localdeprestacaoid" alias="LocalPrestacaoId" /> 

    <attribute name="new_idlocalprestacao" alias="Idlocalprestacao" />

    <attribute name="new_localprestacao" alias="LocalPrestacao" />

    <attribute name="new_nomecomercial" alias="NomeComercial" />

    <attribute name="statecode" alias="StateCode" />

    <attribute name="statuscode" alias="StatusCode" />

    <attribute name="createdon" alias="CreatedOn" />

    <attribute name="modifiedon" alias="ModifiedOn" />

    <attribute name="new_elementopertencentea" alias="ElementoPertencenteA" />

    <attribute name="new_accountid" alias="AccountId" />

    <attribute name="new_codigopostal" alias="CodigoPostal" />

    <attribute name="new_leadid" alias="LeadId" />

    <filter>      <condition attribute="modifiedon" operator="gt" value="1900-06-01T09:06:24Z" />

    </filter>

    <link-entity name="new_codigopostal" from="new_codigopostalid" to="new_codigopostal" link-type="outer" alias="TabelaCodigoPostal">

      <attribute name="new_codigopostal" alias="NomeCodigoPostal" /> 

    </link-entity>

  </entity>

</fetch>

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,858 questions
Accepted answer
  1. Smaran Thoomu 14,870 Reputation points Microsoft Vendor
    2024-09-03T18:45:01.5166667+00:00

    Hi @Catarina Leiria

    Thank you for reaching out to us regarding the issue you are facing when fetching data from Dynamics CRM and copying it to a DW. Based on the information you provided, it seems that you are experiencing two issues:

    1. One of the columns ("LeadId") appears in the preview data in the source of the copy activity, but it is not created in the DW.
    2. One of the columns is duplicated, creating the original column and its alias ("new_localdeprestacaoid" and "LocalPrestacaoId").

    To address the first issue, it is possible that the column is not being created in the DW due to a data type mismatch or a naming conflict. You can try renaming the column in the source query to see if it resolves the issue. Alternatively, you can try explicitly specifying the data type of the column in the source query to ensure that it is created correctly in the DW.

    To address the second issue, it is possible that the column is being duplicated due to a naming conflict or an alias conflict. You can try renaming the column in the source query to see if it resolves the issue. Alternatively, you can try explicitly specifying the alias of the column in the source query to ensure that it is created correctly in the DW.

    If the above solutions do not resolve the issues, you can try using a staging table to copy the data from Dynamics CRM and then transform the data in the staging table before copying it to the DW. This can help you to identify and resolve any issues with the data before it is copied to the DW.

    I hope this helps! Let me know if you have any further questions or concerns.

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.