Search code examples
odbcazure-synapselinked-serverlinked-service

Setting up Azure Synapse Analytics Linked Service to ODBC linked server

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

enter image description here

Azure Synapse Linked Service

enter image description here

Solution

  • 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:

    enter image description here

    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.

    enter image description here

    I created linked service for SQL server through selfhosted IR:

    enter image description here

    I am having data lake storage linked service. I create new pipeline:

    enter image description here

    Performed copy data activity following below procedure:

    I created sql server dataset using sql server linked service and selected dbo.product table as source:

    enter image description here

    Source data preview:

    enter image description here

    I selected synapse default storage, selected delimited text and selected the folder as sink.

    enter image description here

    I run the Copy activity. It run successfully.

    enter image description here

    Delimited file is stored storage.

    enter image description here

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