Setting up Azure Synapse Analytics Linked Service to ODBC linked server

Question

A vendor provided access to their server via ODBC connector that created a SYSTEM DSN on my local machine. I would like to replicate the data in Azure Synapse data lake for analysis. The Azure Synapse Linked Service configuration does not allow me to reference the newly linked server shown in image 1.

There are possibly a few ways to connect the two

1. Add connection parameters in Azure Synapse to reference the linked server
2. Setup the integration as ODBC connector instead of SQL server
3. (Last resort) Create a duplicated image of the data to connect to Azure Synapse

Guidance and preference would be greatly appreciated on the best method

Local SSMS

Azure Synapse Linked Service

Answer 1

AFAIK we can't connect to the linked servers of SQL server. If we want to copy the data of linked server, we can copy the tables into database and copy the data to blob storage.

For that I followed below procedure:

I created ODBC linked server in SQL server. I retrieved some data. Image for reference:

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 selected synapse default storage, selected delimited text and selected the folder as sink.

I run the Copy activity. It run successfully.

Delimited file is stored storage.

In this way you can copy data from linked server of sql server.