To do ETL, we need to have a source to query data and a target (often called a sink) to land the transformed data. Our first recipe will create a simple SQL Azure database that will be used as both the source and sink in all recipes of this chapter.

Getting ready

In Chapter 1, Getting Started with Azure and SSIS 2019, you were shown how to install SQL Server on-premises, SQL Server Management Studio (SSMS), and Visual Studio 2019 with the SSIS extension. This chapter will show you how to set up SQL Server in Azure and the recipes will use this version of SQL Server.

How to do it…

In a browser, go to the Microsoft Azure portal using the following URL: https://portal.azure.com/#home.

On the main portal page, we'll create a resource group to group all Azure services together. It's much easier to find and manage them later.:

1. From the main portal page, click on the + sign to create a new resource, as shown in the...

This recipe will show you how to connect to our Azure SQL database from your computer.

Getting ready

This recipe assumes that you have installed SSMS.

How to do it…

This is a very short and simple recipe! Let's begin by opening SSMS:

1. We're going to connect to the Azure database that we created in the previous recipe, Creating a SQL Azure database. Log into your Azure subscription and navigate to the ETLInAzureCookBook resource group.
2. Click on the AdventureWorksLT SQL database to open its blade. Copy the server name found at the top right as shown in the following screenshot:

   Figure 2.14 – Get the server name from the Azure subscription

3. Using SSMS, log into the server using the username and password that we used in Step 7 of the previous recipe: Creating a SQL Azure database. Once connected, the Object Explorer shows the database and its related objects. Expanding the Tables folder lists...

This recipe will show you how to build a simple ETL package that does the following:

• Extract data from the AdventureWorksLT SalesLT.Customer table.
• Transform data: Concatenating all customer-name-related information into a single column called FullName.
• Load the data into a new table called SalesLT.CustomerFullName.

The table we create will be used in the final recipe in this chapter, Loading data before its transformation, to explain ELT.

Getting ready

This recipe assumes that you have installed SSMS, Visual Studio 2019, and SSIS.

How to do it…

Let's begin by opening Visual Studio 2019:

1. Select Create a new project from the main screen.
2. Select Integration Services Project from the screen that appears, as shown in the following screenshot:

   Figure 2.16 – Visual Studio – creating a new Integration Services Project

3. As shown in the following screenshot, name the project AdventureWorksETL...

ELT is very similar to ETL, but with a crucial difference: the order of the transform and load steps are inverted. This is very useful with big data in the cloud or when we do not have an ETL tool on-premises. This recipe will be much simpler than the previous one, as we'll implement ELT using a database, so no tools are involved here except for calling the ELT task.

It also relies on the previous recipe, Creating a simple ETL package, since we're going to use the SalesLT.CustomerFullName table data to implement the ELT pattern.

There are essentially two parts to this recipe:

1. Extract and load data into our data lake. Here, we don't have a real data lake; we're using AdventureWorksLT on Azure to mimic the data lake concept.
2. Transform the data inside the database using a simple SQL script. We're going to add the FullName column to the SalesLT.Customer table and update it using this script.

Getting...