Copy Job from on prem SQL to Fabric SQL Database Error

[aadhilkhan]

Hi there Fabric Experts.

I am trying to copy data(using Copyjob) from SQL on prem to fabric SQL database. My connection works just fine and I can connect to the datasource,view the tables, select incremental column and select my Fabric SQL as a destination(the fabric SQL database exists in the same workspace).

However when I run the copyjob it fails with the below error:

Fabric activity failed. ' Error occured while trying to get FabricSqlConnectionInfo. Please retry later. Detailed Message: 'StatusCode: NotFound, WorkspaceId: myid, ArtifactId: sqlID, Error Code: ItemNotFound, Message: Could not found the requested item,"errorCode":"ItemNotFound","message":"Could not found the requested item"}' '.

Note, ids are masked due to security.

It seems to not be able to find the Fabric SQL database although it's created and I can select it as my destination on the CopyJob UI. I can also view and query the Fabric SQL database perfectly from the workspace. The Copyjob just does not seem to find it while running.

My aim is to use Fabric Translytical Task flows to update data on Fabric SQL based on the inital load.

Is this a known issue?

Appreciate any help on it.

Thanks

[v-venuppu]

Hi @aadhilkhan ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu]

Hi @aadhilkhan ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

This is a known limitation with Copy Job --> Fabric SQL DB, where the destination artifact may not be resolvable at runtime even though it is selectable in the UI. As suggested, using Mirroring is the recommended alternative for on-prem SQL sources, as it reliably syncs data into Fabric without Copy Job resolution issues. Mirroring continuously replicates data and does not overwrite translytical changes made in Fabric SQL tables.

Here are few Microsoft Documentations that will help:
Mirroring - Microsoft Fabric | Microsoft Learn

What is Copy job in Data Factory - Microsoft Fabric | Microsoft Learn

[lbendlin]

PLease note that RequestID is sufficient to uniquely identify your transaction - better mask that in the future too.

Have you considered using Mirroring instead?

[aadhilkhan]

Hi @lbendlin .

Thanks for your response. Let me give that a try.

My aim is to use Fabric translytical task flows to update Fabric SQL with the initial data, will Mirroring now overwrite these changes?