Account
Skip to main content

Implementing Fabric SQL Database Using Data Factory

BimlFlex implements a SQL database in Microsoft Fabric through Data Factory. A Fabric SQL Database connection is a cloud enabled connection that serves as a source for Data Factory copy activities and as a landing target that copy activities load.

Connection Configuration​

Configure your Fabric SQL Database connection from within the BimlFlex Connections editor:

Fabric SQL Database Connection:

  • Set System Type to Fabric SQL Database
  • Enable the Cloud option for the connection
  • Configure Integration Template to Data Factory Source -> Target
  • Set External Reference to the Fabric connection ID of the connection to the SQL database (every connection in Fabric has an internal ID that BimlFlex uses to reference it)
  • The Catalog of the connection names the SQL database

:::note UI Reference

At this point, the BimlFlex App displays the Connections editor for the Fabric SQL Database connection. The form shows System Type set to Fabric SQL Database, the Cloud option enabled, the Integration Template set to Data Factory Source -> Target, and the External Reference field containing the Fabric connection ID. Navigate to Connections in the BimlFlex App and select your Fabric SQL Database connection to review these settings.

:::

note

The External Reference carries the Fabric connection ID that the generated linked service references. This ID can be found in the Fabric portal and enables BimlFlex to properly reference the connection when generating Data Factory pipelines.

Generated Output​

For a Fabric SQL Database connection on a Microsoft Fabric project, BimlFlex generates the following artifacts:

Artifact TypeDescription
Fabric SQL Database Linked ServiceOne linked service per connection, named by the connection. It carries the Fabric connection ID from the External Reference and the database from the Catalog. It carries no connection string, because the Fabric connection carries the credential.
Table DatasetOne dataset per connection over the connection's linked service. The schema and the table are dataset parameters (TargetSchemaName and TargetObjectName).
Copy SourceReads from the SQL database with the source query BimlFlex generates for the object and passes the target schema and object name as parameters. The source also carries a query timeout and additional columns.
Copy SinkLoads the SQL database as the landing target. It receives the target schema and object name as parameters (TargetSchemaName and TargetObjectName) and carries the pre copy script, by default a statement that truncates the target table.

In the generated pipeline, the copy source and the copy sink carry the dataset inline as dataset settings: the database, the schema and the table, with the Fabric connection as an external reference and no inline linked service.

A parameter lookup that runs its SQL on a Fabric SQL Database source is a Script activity that references the linked service named by the connection.

The generated copy source and copy sink also carry the SQL Server copy members, each emitted only when set:

  • Copy Source: StoredProcedure, StoredProcedureParameters, ProduceAdditionalTypes, IsolationLevel, PartitionOption and PartitionSettings
  • Copy Sink: StoredProcedure, TableType, StoredProcedureParameters, StoredProcedureTableTypeParameterName, TableOption, SqlWriterUseTableLock, WriteBehavior and UpsertSettings

BimlFlex sets none of these members when it generates the copy source and copy sink for a Fabric SQL Database connection.

BimlFlex Documentation​

External Resources​