How to bring on-premise SQL Server data into Azure Fabric Lakehouse

On-premise data gateway - one of the important feature/service Azure Fabric platform is using to bring your on-premise data into Azure cloud. The data movement is secure and encrypted.

You can review my my earlier post https://splaha.blogspot.com/2025/03/use-on-premise-gateway-in-fabric-and.html  where I explain how to configure On-premise data gateway using Step 1 and Step 2.

In this exaple, I'm going to use the same data conenction to pull my on-premise SQL tables using custom join SQL into Lakehouse.

The entire post is self explanatory with step-by-step snapshot. I hope this will help you to understand each and individual steps as well as to execute the same at your end.


Let's start with my on-premise SQL server details. As you can see the below picture, it talks about the database Employee where there are two tables EMP and DEPT. I'm going to use the same SQL into Fabric to pull the data.


I assume you already created the on-premise data gateway using my earlier post. In case not, then  open Fabric >> Settings >> Manage connection and gateways. If you already have the on-premise data gateway, then go to the next step - Creationn of Dataflow.



Creation of dataflow: Go to your workspace >> Click on + New Item >> Select Dataflow Gen2



Select More/SQL Server





Select Data gateway


Specify your Server details, database name and I place my custom SQL under SQL statement box.



Just to make sure you place the correct credential as in the below picture. 
Click Next to let Fabric to connect your local on-premise data and bring it into Fabric.

You can see how fabric use functions to pull your data. You can apply filter, sorting aginst the pull data.


Click on Data destination + icon to select your Destination (where this data is going to save). 

In the following example, I choose Lakehouse as I want to store the table in Lakehouse.



Click Next. In case, if you want to change connection credential, you can clcik on Edit connection and update your connection there.


Inside the Lakehouse, Select the SQL DB and specify your table name. You have option to add the data in the existing table, or create a new table. For the first time, definately will go ahead and create a new table as below.



Here, it is standard ETL UI, mapping UI. Map the sourec column and data type with destination column name and datatype. Once done, click on Save settings. 


You did it :)



Now, if you go to your Lakehouse and click on the newly created table, you can see the data pull from your on-premise store.



I hope you like this post. Comments, request, advice are always welcome :)

Comments

Post a Comment

Popular posts from this blog

How to fix Azure DevOps error MSB4126

Image
Setting up build and release pipeline always tedious and we face many build related issues. Here is one common error I face couple of times while setting app Web or windows apps in Azure DevOps After placing the code in repos, and creating the build pipeline and run it, you can face the following error ##[error]D:\a\8\s\<your solution file>.sln.metaproj(0,0): Error MSB4126: The specified solution configuration "release|$(BuildPlatform)" is invalid. Please specify a valid solution configuration using the Configuration and Platform properties (e.g. MSBuild.exe Solution.sln /p:Configuration=Debug /p:Platform="Any CPU") or leave those properties blank to use the default solution configuration. To understand what is target platform defined in your project you need to visit or open the solution(.sln) file and review the following sections. In my case, this has been defined as x86 as target platform.         ...
Read more

SharePoint Admin Center

Image
 SharePoint Admin Center allows you to view your organization's SharePoint site including communication sites, channel sites, and sites that belong to Microsoft 365 group. This is same like Site Admin in earlier days of SharePoint versions. You can search organizational site, sort them or filter sites the way you want. Easy way to find and apply/change site settings. Under Policies, click on Sharing - This will allow you to create rules/ set control sharing at the organization level in SharePoint and OneDrive Here, in this page, you can apply settings to restrict how users are allowed to access content in SharePoint and OneDrive. Term store, the word getting populated from SharePoint 2010. You can feed metadata of metadata information of your organization level, program level, project level in Term store. helps your organization's knowledge better.  AI soon will play a vital role here and using AI, you can acyually find collect and process and finally yoour organization's d...
Read more

Entity Framework common error - no such table: __EFMigrationsHistory + ConnectionString property has not been initialized + certificate chain was issued by an authority that is not trusted

Today, I'm going to discuss about few common errors developer face while executing migration using Entity Famework. For example one common error is  SQLite Error 1: 'no such table: __EFMigrationsHistory' . This is kind of limtation of SQL Lite to work with memory management. Below the error infomation. Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion") Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: __EFMigrationsHistory'.    at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)    at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()    at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()    at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()    at Microsoft.Data.Sqlite.Sqlit...
Read more