How to copy multiple databases from SQL Server to Azure Synapse Analytics

Question

I have an SQL Server which contains 27 Database. I want to create a data pipeline with Azure Synapse Analytics to copy the data from the DBs. Should I create a linked Service for every database or I can add a dynamic content in the Database name to get all the databases ?

I tried to add a dynamic content with this SQL request EXEC sp_databases but it doesn't work.

Answer 1

To copy data from multiple databases in SQL Server to Azure Synapse Analytics, you can create a single linked service for the SQL Server and use dynamic content to specify the database name in the copy activity.

• In the linked service, create a parameter. In this demo, I have create a parameter named database.

• Then give the parameter name as a dynamic content in database name. @{linkedService()/database}.

• Create a dataset for database. It will ask for the database name.

This way, by creating a parameter in the linked service and using it in the dynamic content expression, you can easily change the database name without having to create different linked services for each database.