A data warehouse is a central repository where structured data from various sources are consolidated and stored for reporting and analysis. Microsoft Fabric, being an end-to-end analytical platform, offers its own next-generation, lake-centric data warehouse. It is built within the Data Lake and supports multiple open formats. Fabric data warehouse offers seamless data collaboration between businesses, end users, and data engineers/scientists, with no compromise on security, data integrity, and governance. Some benefits of using Warehousing in Fabric are:
- Unified platform for warehousing, analytics, visualizing, etc.
- High-performance querying using Massive parallel processing (MPP)
- Security and governance using granular access control
- Scalable and flexible
- Seamless reporting
The Fabric Workspace supports two distinct warehousing items - The Fabric Data Warehouse, and the SQL analytics endpoint. Let's see how they differ in terms of their behavior. The former is labeled in the Fabric workspace as "Warehouse" type, and this can be compared with a traditional data warehouse that can fully utilize transactional capabilities like DDL and DML query support and can act as a fast and simple data warehouse solution. The Warehouse can also be populated by any of the data ingestion methods such as COPY INTO, data pipeline, etc., or through the cross-database ingestion option using T-SQL. On the other hand, the SQL analytics endpoint is an autogenerated endpoint, which can be used to transition from the Lakehouse to the SQL view of the same Lakehouse. The main difference is also that with the SQL analytics endpoint, the T-SQL command can define and query data objects, but cannot perform manipulation.
In this blog post, we will be seeing how KingswaySoft components can be used to easily work with both the endpoints above. We will be primarily using KingswaySoft SQL Server Connection Manager, Premium SQL Server Source, and Premium SQL Server Destination components for this.
Setting up Fabric Warehouse authentication parameters for SQL connectivity
When you are logged in to your Fabric workspace, you can create your Warehouse, Lakehouse, etc., by clicking on the "New Item". Once you have configured them, you can find them in the list under your workspace as shown below. The "type" field shows what the object is, and as you can see, the Warehouse is on its own, whereas the SQL analytics endpoint is generated under your Lakehouse.
In order to facilitate connectivity from our SQL Server SSIS connection manager, we would need to get the SQL endpoint for either the Warehouse or for the SQL analytics. The SQL endpoint Connection String acts as a service URL that can be used in our connection manager within SSIS to connect and read/write to the data objects in it. You could navigate to either one (the endpoint Connection String would be the same for both the Warehouse and Lakehouse since it is created under the same Fabric workspace) and click on the gear icon to open Settings. Here, you can choose "SQL endpoint" and copy out the SQL Connection String.
We will be using this as the Data Source/host URL in our SSIS connection. Next, we move over to the Azure portal to set up permissions for the OAuth App. In your Azure tenant, under Entra Id, you could create a registered App and set up the redirect URL as well as generate the Client Secret. Once done, you could navigate to API permissions and provide the below-delegated permission.
Note that you could provide additional Fabric or Warehouse permissions as required on top of the above. Once you are done with the Azure portal, save the Client Id, tenant Id, Client Secret, and the redirect URL, and let's move on to our SSIS package.
KingswaySoft SQL Server Connection Manager configuration
In your SSIS package, create a new SQL Server connection manager, and enter the Connection String you had copied from the Fabric workspace as the "Data Source/Host". Then, choose the Authentication Mode that you wish to work with. In our example, we are using the Authorization Code type, which can be used to create a token file for authentication.
Once done, click on "Sign in & Authorize" to log in and authorize your Azure App to access the Fabric data from the warehouses. When the token is created, you can select the "Initial Catalog/Database" in the connection manager. This populates the available Database objects in your Fabric workspace, and you can choose either the Warehouse to work with the warehouse DB, or the Lakehouse to query the SQL analytics endpoint. Note that only a single Database can be chosen in one connection manager, so you would need two connection managers if you wish to work with both warehouse and SQL analytics endpoint respectively.
Now, since the SQL analytics endpoint only supports querying the data, by using the connection manager in which you have chosen the Lakehouse database, you can use the Premium SQL Server Source component, as shown below, to read from the dataset. Note that the Data Source we have in our example shows Custom Command, and this is because we have modified the preset SQL statement. You could just click on the drop-down to get the list of tables in it.
With the connection manager that points to the Warehouse database, you can use the Source component as shown above and also run DML and DDL statements by using the Premium SQL Server Destination component or the SQL server command task.
Conclusion
As shown in our demonstration above, you can easily connect to your Fabric Warehouse data objects using KingswaySoft. We offer support for modern authentication approaches that make the connection easy to configure as well as future-proof.
We hope this has helped!