Azure Synapse Step by Step Sink to SQL Database

Question

Azure Synapse Step by Step Sink to SQL Database

Matthew Boustead 40

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!

Subashri Vasudevan 11,231 Reputation points

2023-11-25T12:42:54.02+00:00
Hi, couple of questions to understand the problem.

is the SQL database you are talking about is on your local machine or on Prem server ?

What's your sink, is it a CSV file on Azure storage?

You want to copy data from file to SQL table , is that right

When you say you are prompted to enter schema and table name, it means that your data set has parameters fo those fields.

Reg creating table, yu can use auto create table option in the copy activity or Data flow activity.

Pls clarify the doubts , so that we can assist better
Matthew Boustead 40 Reputation points

2023-11-27T08:05:47.16+00:00
Hi Suba,

The SQL database is on my local machine

My sink is combined CSV files

And yes i wish to move these combined CSV files into an SQL table so they can then be exported into Power BI

I hope these answers help and if you need any further information then just let me know.

1 answer

Your answer

Subashri Vasudevan 11,231 Reputation points

2023-11-25T12:42:54.02+00:00

Hi, couple of questions to understand the problem.

is the SQL database you are talking about is on your local machine or on Prem server ?

What's your sink, is it a CSV file on Azure storage?

You want to copy data from file to SQL table , is that right

When you say you are prompted to enter schema and table name, it means that your data set has parameters fo those fields.

Reg creating table, yu can use auto create table option in the copy activity or Data flow activity.

Pls clarify the doubts , so that we can assist better
Matthew Boustead 40 Reputation points

2023-11-27T08:05:47.16+00:00

Hi Suba,

The SQL database is on my local machine

My sink is combined CSV files

And yes i wish to move these combined CSV files into an SQL table so they can then be exported into Power BI

I hope these answers help and if you need any further information then just let me know.

Answer 1

Subashri Vasudevan 11,231

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.

Matthew Boustead 40 Reputation points

2023-11-28T15:25:52.87+00:00

I am worried a dedicated pool will result in a sharp increase in the cost of this software?
Subashri Vasudevan 11,231 Reputation points

2023-12-05T16:56:12.1133333+00:00

It incurs cost even when you use a self hosted integration runtime to connect to on pren db. With synapse dedicated SQL pool, you can pause the database when not in use so that you can save some costs there. By having data in synapse dedicated SQL pool, it would be straight forward to connect to power bi to generate reports.

Share via

Azure Synapse Step by Step Sink to SQL Database

1 answer

Your answer