Welcome to the Microsoft Q&A platform and thank you for posting your question here.
As far as I know, we can't connect to linked servers in SQL Server. If we want to copy data from a linked server, we can copy the tables to a database and then copy the data to blob storage.
To do this, I followed these steps:
- I created an ODBC linked server in SQL Server.
- I retrieved some data.
I copied the linked server table to database using below code:
SELECT * INTO db.dbo.product FROM linkedserver.AZURE SYNAPSE.dbo.product
The table is copied successfully. I created synapse workspace and created selfhosted IR and connected successfully.
- I created linked service for SQL server through selfhosted IR:
- I am having data lake storage linked service. I create new pipeline. Performed copy data activity following below procedure:
- I created sql server dataset using sql server linked service and selected dbo.product table as source:
Source data Preview:
- I run the Copy activity. It run successfully. In this way you can copy data from linked server of sql server.
I hope this helps. Please let me know if you have any further questions.
If this answers your query, do click
Accept Answer and
Yes for was this answer helpful. And, if you have any further query do let us know.