question

RajMaddala-5812 avatar image
0 Votes"
RajMaddala-5812 asked NG-1408 commented

Get data from Azure synapse link for dataverse using SSIS

Hi All,

We had setup Azure synapse link for dataverse to push data from D365 to datalake and serverless pool.

We have BI environment where we need to pull this data from serverless pool to Azure SQL DB.

We have very limited budgets. We don't want to use ADF or dedicated pool(SQL).

We want to use SSIS.

How can I connect to synapse serverless pool from SSIS?

The SSIS packages need to be scheduled in SQL Server agent(Azure VM) which needs SQL authentication.

What are the options

  1. Connect using SSIS

  2. Authentication method

  3. Agent job setup


Thanks in advance





azure-synapse-analyticssql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RajMaddala-5812 Hi, We are in the similar situation.

Can you please help if you found the solution.

I can't find SqlClient Data Provider option to choose.

thanks in advance.

0 Votes 0 ·

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered RajMaddala-5812 commented

Hi @RajMaddala-5812,

You may try to use ADO.NET connection manager as the source and then load the data.

In the Connection Manager dialog box, do the following things.

For Provider, select the SqlClient Data Provider.
For Server name, enter the dedicated SQL pool name.
In the Log on to the server section, select Use SQL Server authentication and enter authentication information.
In the Connect to a database section, select an existing dedicated SQL pool database.
Click Test Connection.

Here is an official documentation you may refer to.

load-data-to-sql-data-warehouse

Regards,

Zoe Hui


If the answer is helpful, please click "Accept Answer" and upvote it.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ShaikMaheer-MSFT

Thanks for the reply but I don't have dedicated pool. I just have serverless pool

1 Vote 1 ·

Hi @RajMaddala-5812 ,

Just checking in to see if the above answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·