search

microsoft / Dynamics-365-FastTrack-Implementation-Assets

Dynamics 365 FastTrack Implementation guides
MIT License
316 stars 202 forks source link

IncrementalExportSQL doesn't work #224

Open GeewDev opened 1 year ago

GeewDev commented 1 year ago

Hello,

I deployed cdmutil as Azure Function and evertything is working fine. Views are created in Synapse Serverless Pool, I can query data and ejoy life.

Anyhow, I want to transfer data from D365 data lake to Azure SQL Database. I downloaded ADF pipeline templates. Full export works fine but I have serious problem with incremental load.

If I try to trigger pipeline I recieve following error: { "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid object name 'd365._cdc_CustTable'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'd365._cdc_CustTable'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=208,State=1,Message=Invalid object name 'd365._cdc_CustTable'.,},],'", "failureType": "UserError", "target": "CDCCopy", "details": [] }

CustTable is just example, I recieve same error for every table in my data lake. Views with preffix cdc don't exists in my Synapse Serverless Pool, I have regular named views.

Here is my configuration in Azure Function App: DDLType: SynapseView ParserVersion: 2.0 (I tried 1.0 with same effect) ManifestURL: ends with Tables/Tables.manifest.cdm.json

Do you have any idea what's wrong?

Thank you in advance Mateusz

emurakami commented 1 year ago

Hi Mateusz, There is a disconnect between the way CDMUtil is creating the change feed views in Synapse, and the way the Full Export pipeline is populating the CDCTableName in the SynapseToSQLTables in the SQL database.

First, you have to make sure that you're building both the main table views and change feed views by running the CDMUtil twice. This explains it better than the readme: SynapseToSQL_ADF - Steps 4

Assuming you used cdc as the change feed table schema name, modify the PostCopyScript activity in the Full Export pipline with this updated CDCTableName Declare @CDCTableName varchar(100)= 'cdc.@{item().TABLE_NAME}'; alternatively add a parameter to the pipeline for the change schema name Declare @CDCTableName varchar(100)= '@{pipeline().parameters.CDC_SCHEMA}.@{item().TABLE_NAME}';