Populate Azure Synapse Analytics from Azure SQL databases using SSIS

Populate Azure Synapse Analytics from Azure SQL databases using SSIS

In this article, we will learn how to populate data from Azure SQL database into Azure Synapse Analytics using SQL Server Integration Services (SSIS).

In this article, we will learn how to populate data from the Azure SQL database into Azure Synapse Analytics using SQL Server Integration Services (SSIS).

Introduction

In the previous parts of the Azure Synapse Analytics article series, we learned how to create a SQL on-demand pool, dedicated SQL pool as well as Apache Spark pool. Once the pool gets created, we also learned earlier how to create tables in those pools. One can create external tables as well as native tables in those pools. There are different mechanisms to populate these tables from a variety of sources. One of the most basic use-cases is to populate Azure Synapse Analytics tables in dedicated SQL pools from data hosted in the Azure SQL database. This use-case is applicable when one intends to analyze large scale transactional data hosted in Azure SQL Database using the distributed, parallel processing, OLAP style engine of Azure Synapse Analytics with the dedicated SQL pool. In this article, we will load data from Azure SQL Database into Azure Synapse Analytics using SQL Server Integration Services (SSIS).

Pre-requisites

There are certain pre-requisites that need to be in place before we can focus on the actual task of using SSIS to populate data from source to destination. The first thing we need in place is the source and destination repositories. In this case, the source would be the Azure SQL database with some sample data in it. And destination would be Azure Synapse Analytics workspace with a dedicated SQL pool created in it. You may have an empty pool or a pool with data already in it. Either way, it would work. It is assumed these source and destination repositories are in place along with sample data in the Azure SQL Database instance.

Next, we need SQL Server Data Tools with the templates related to SSIS, so that we can create an SSIS package that can connect to Azure-based source and destination repositories. It is assumed that these tools and frameworks are already installed on the development machine where we would be creating our SSIS package.

azure integration services (ssis) sql azure

What is Geek Coin

What is GeekCash, Geek Token

Best Visual Studio Code Themes of 2021

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server

In this article, you learn how to set up Azure Data Sync services. In addition, you will also learn how to create and set up a data sync group between Azure SQL database and on-premises SQL Server.

Migrating SQL workloads to Microsoft Azure: Services Selection

In this article, we will go through the different database services that are provided by Microsoft Azure to help you in selecting the proper service that can serve your SQL workload when migrating it to Microsoft Azure

Demo: Configure Azure SQL with Azure CLI | Azure SQL for beginners (Ep. 17)

In this video, see how to configure your Azure SQL connectivity leveraging the Azure CLI and PowerShell notebooks in Azure Data Studio. For the full Azure SQ...

Sourcing data from Azure SQL Database in Azure Machine Learning

In this tutorial, we will show how to source data from Azure SQL Database to use in a Machine Learning workflow.