Copy Job (Preview) ends with error: Fabric Capacity not found

[ToddChitt]

I just now enabled a free Fabric trial on an existing Workspace.  I created a new Copy Job (Preview) to pull from an Azure SQL Database and sink into a new Fabric SQL Database. It reads the source meta data and table data just fine and makes the sink conneciton just fine. But when I run it, it throws an error on every table: error 404: Failed to report Fabric capacity. Capacity is not found

Any ideas? Thanks in advance.

[ToddChitt]

I am selecting the Azure SQL Database option, and pasting in the connection string that you can get from the "Copy SQL Connection string" option of the SQL analytics endpoint artifact that comes with every database. 

(Pro tip: I use these connecton strings and set up 'Registered Servers' in SQL Server Management Studio. Being a consultant, I have a few dozen created and sorted into folders. All Warehouses, Lakehouses, and now Fabric SQL Databases in any one workspace are available behind that one single SQL Endpoint. They show up as named databases under the server. You can run cross-database queries like for moving data from Lakehouse to Warehouse. I love it!)

This is my setup:

I am getting a different error today:

Note that I am using my organizational account, which A) is an Admin on the Workspace, and B) was used to create the Fabric SQL Database. Yet that account does NOT have permissions to create objects. I get the same error trying to create a table from SSMS:

THAT is a BUG. If I CREATE the database, I MUST HAVE ADMIN rights on it. 

This Copy Job is fraught with issues. IMHO, it should never have been released to preview. So far, in one day I have found:

1) Using a Warehouse or Lakehouse as the destination, there is no way to go back and edit the tables or column mappings.

2) Using a Lakehouse destination, it does not support column names (and I assume table names) with spaces (or other special characters, also assumed). Data Flows correct this for you, but only for NEW tables. 

3) You cannot specify an EXISTING Lakehouse or Warehouse. So heaven forbid you mess up your table mappings, there is no way to create a NEW Copy Job that sinks to the warehouse you already have. You have to create a whole NEW Warehouse or Lakehouse. And this is BEFORE you specify FULL or DELTA. So how does DELTA work?

4) Using a Warehouse destination wants you to configure some other Azure storage, but GIVES YOU NO ABILITY TO DO SO. How do I make that work?!?

[ToddChitt]

The original error of "Fabric capacity not found" resolved itself within 24 hours. I think Microsoft had to 'wake up the monks' to record the new Free Trial capacity. I just assumed that when it let me proceed that it would let me do everything. Such was not the case. The original default diagnostic response of "please reboot and see if that fixes it" has been changed to, "wait 24 hours to see if it catches up propogating across Microsoft data centers." 🙂

Regarding the second issue (and this was a bit of learning on my part): The new Fabric SQL database is reachable via the same Connection String used for Warehouses and Lakehouses, but that is an ANALYTIC Endpoint, so NOT write-able. If you open the SQL Database in a Fabric Browser, you have a menu option to "Open in SSMS" and that gives you a SQL Endpoint and database name that you can use in SSMS. Note that you need to connect directly to the database as you do not have access to the master db. By targeting the SQL Endpoint in the Copy Job (Preview) it worked.

If, like me, you don't like to have a GUID appended to your database name when it is accessed via the SQL Endpoint, then vote for my Fabric Idea here: Microsoft Idea

[Anonymous]

Hi @ToddChitt 

I don't see the new Fabric SQL Database available as an option for destination. And the document ( What is Copy job (preview) in Data Factory ) doesn't list it as a supported source and destination. Can you show a screenshot of your page? (Note: remove sensitive data)

You can try sinking data into a Fabric lakehouse. Although Fabric warehouse is also listed as supported, currently there is a known issue with that. Fabric Lakehouse should work.

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!