Azure Synapse Step by Step Sink to SQL Database

Matthew Boustead 40 Reputation points
2023-11-24T08:23:22.4366667+00:00

Hi there,

I am struggling to get my transformed data from the 'sink' to my SQL Database. I am using CSV data types - what sink type to use (integration dataset, inline).

I am pretty sure I want to press inline as that is the only option that allows me to select the sql database with the linked service I am wanting to use as well. On the settings tab, schema name and table name pop up could you explain what I am putting into these.

Finally I am unsure how to create tables in my SQL database

Any help on any of these points would be greatly appreciated, thank you!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,653 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Suba Balaji 11,206 Reputation points
    2023-11-28T15:21:24.16+00:00

    Hi,

    So, you want to move data from CSV file to a SQL table. Since you say you want to use power bi, instead of having a SQL server at on Prem, you can consider creating a dedicated sql pool table in synapse which can be directly accessed from power bi. So we can eliminate the SQL server at your local machine.

    I'm assuming your CSV file is stored in azure storage. In that case you can go through this video (https://youtu.be/K2xiGZ2yQZs?si=QzWaIOZyseD2onnk) and this page(https://learn.microsoft.com/en-us/azure/synapse-analytics/quickstart-copy-activity-load-sql-pool) to know how you can load data to synapse from CSV file.

    While you use a copy activity, in the sink, you may choose the table option 'auto create table' if you want to create table during runtime by the copy activity itself.

    Hope this helps.

    Please let us know, if you need more details. Would be happy to assist.