You're reading from Azure Data Factory Cookbook

Product typeBook

Published inDec 2020

PublisherPackt

ISBN-139781800565296

Edition1st Edition

Tools

Azure Data Factory

Concepts

Data Streaming

Authors (4):

Dmitry Anoshin

Dmitry Foshin

Roman Storchak

Xenia Ireton

View More author details

Chapter 6: Integration with MS SSIS

SQL Server Integration Services is a highly capable ETL/ELT tool that is built into Microsoft SQL Server. Often, it is used to move and transform data on-premises and in cloud environments. Its features somewhat resemble the main features of Azure Data Factory (ADF). At the same time, ADF has high availability and is massively scalable.

ADF adds convenience as it allows the execution of SSIS packages in Azure-SSIS integration or on-premises runtimes. With this tool, you can leverage existing SSIS packages and use familiar development tools.

Running SSIS packages is an essential feature in ADF. In this recipe, we will create a basic SSIS package, deploy it to a managed Azure SQL database, and then trigger its execution in ADF.

In this chapter, we will cover the following topics:

Creating a SQL server database
Building an SSIS package
Running SSIS packages from ADF

By the end of the chapter, you will be able to build...

Technical requirements

You need to have access to Microsoft Azure. An Azure free account is sufficient for all recipes in this chapter. Also, you have to download and install Visual Studio 2019. During installation, check the Data storage and processing and SQL Server Data Tools fields. You can use the following tutorial: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15#ssdt-for-visual-studio-2019.

Creating a SQL Server database

SSIS is part of the SQL Server ecosystem. Hence, we need to create a SQL Server and a database and fill it with the data that will be used in the following recipes to deploy and run SSIS packages.

Getting ready

To get started with your recipe, log in to your Microsoft Azure account.

We assume that you have pre-configured a resource group and storage account with Azure Data Lake Gen2.

How to do it…

Let's prepare a SQL Server database. Later, it will be used to run the SQL Server Integration Service package. Perform the following steps:

Go to SQL databases on Azure, as shown in the following screenshot:
Figure 6.1 – Adding a SQL database
Next, click + Add and then fill in the Database name field and click Create new, as shown in the following screenshot:
Figure 6.2 – Setting a SQL Server
Fill in the Server name, Server admin login, Password, and Confirm password fields. Select a Location. Preferably...

Building an SSIS package

SSIS packages are a great way to build ETL/ELT processes with SQL Server. Packages (or projects) store the sequence of steps that are performed to execute an activity. Let's build a package that will connect to the SQL Server database, which stores the movielens dataset, preprocess it, and store the output in a new table.

Getting ready

In order to follow this recipe, you need to finish the Creating a SQL Server database recipe, as we will build an SSIS package that will use both a database and data that we have prepared previously.

Then, you have to install the most recent version of Visual Studio and log in to Azure via Visual Studio. Visual Studio Community edition fully supports all the features that we will use in the following recipes.

How to do it…

Let's build an SSIS package that joins two tables and deploy it to SSISDB in Azure:

Open Visual Studio and create a new project.
Select Integration Services Project...

Running SSIS packages from ADF

SSIS packages are commonly used in on-premises tasks. In order to leverage existing infrastructure and scale up operations, SSIS packages can be run in the cloud. Let's prepare a cloud infrastructure, deploy a package that we created in a previous recipe to the Azure Cloud, and then trigger its execution via ADF.

Getting ready

We assume that you have followed all the previous recipes from this chapter. We will use the outcomes of all of them.

How to do it…

Let's create a SQL database that we will need to store data and host SSIS packages:

Go to the Azure Data Factory interface that you use for learning on the Author & Monitor, Manage, Integration runtimes page (see Figure 6.26).
To add a new integration runtime, click + New:
Figure 6.23 – Integration runtimes in Azure Data Factory
Select Azure-SSIS (Lift-and-shift existing SSIS packages to execute in Azure...

The rest of the chapter is locked

You have been reading a chapter from

Azure Data Factory Cookbook

Published in: Dec 2020Publisher: PacktISBN-13: 9781800565296

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Authors (4)

Dmitry Anoshin

Dmitry Anoshin is a data-centric technologist and a recognized expert in building and implementing big data and analytics solutions. He has a successful track record when it comes to implementing business and digital intelligence projects in numerous industries, including retail, finance, marketing, and e-commerce. Dmitry possesses in-depth knowledge of digital/business intelligence, ETL, data warehousing, and big data technologies. He has extensive experience in the data integration process and is proficient in using various data warehousing methodologies. Dmitry has constantly exceeded project expectations when he has worked in the financial, machine tool, and retail industries. He has completed a number of multinational full BI/DI solution life cycle implementation projects. With expertise in data modeling, Dmitry also has a background and business experience in multiple relation databases, OLAP systems, and NoSQL databases. He is also an active speaker at data conferences and helps people to adopt cloud analytics.
Read more about Dmitry Anoshin

Dmitry Foshin

Dmitry Foshin is a business intelligence team leader, whose main goals are delivering business insights to the management team through data engineering, analytics, and visualization. He has led and executed complex full-stack BI solutions (from ETL processes to building DWH and reporting) using Azure technologies, Data Lake, Data Factory, Data Bricks, MS Office 365, PowerBI, and Tableau. He has also successfully launched numerous data analytics projects – both on-premises and cloud – that help achieve corporate goals in international FMCG companies, banking, and manufacturing industries.
Read more about Dmitry Foshin

Roman Storchak

Roman Storchak is a PhD, and is a chief data officer whose main interest lies in building data-driven cultures through making analytics easy. He has led teams that have built ETL-heavy products in AdTech and retail and often uses Azure Stack, PowerBI, and Data Factory.
Read more about Roman Storchak

Xenia Ireton

Xenia Ireton is a Senior Software Engineer at Microsoft. She has extensive knowledge in building distributed services, data pipelines and data warehouses.
Read more about Xenia Ireton

Other recommended products

Related to this chapter

Azure Data Engineering Cookbook

This book will help you design and implement modern ETL workflows along with data management, monitoring, and security aspects to meet the current organization's needs. You will use various services such as Azure Data Factory, Azure Databricks, Azure Stream Analytics, and Azure Data Explorer to design efficient data processing solutions.

BookApr 2021454 pages

Limitless Analytics with Azure Synapse

This book helps you understand the basic concepts and techniques of using Azure Synapse step-by-step. You'll gradually gain the skills you need to work with data and develop analytics solutions using the Azure analytics platform even with no prior knowledge of Azure.

BookJun 2021392 pages

ETL with Azure Cookbook

This book will take you through hand-on recipes for extracting, transforming, and loading data using big data tools and Azure services such as Data Factory and Azure Databricks. You will learn how to interact effectively with Azure services, along with covering automation with BIML and data profiling in Azure.

BookSep 2020446 pages

Azure Databricks Cookbook

The Azure Databricks Cookbook shows you how to work with the latest as well as older versions of Apache Spark and integrate with various Azure resources for orchestrating, deploying, and monitoring big data solutions. You'll use Azure Databricks to build end-to-end solutions and address challenges in securing, productionizing, and monitoring them.

BookSep 2021452 pages

Hands-On Data Warehousing with Azure Data Factory

Azure Data Factory (ADF) is a Microsoft Azure PaaS solution which supports data movement between many on premises and cloud data sources. This book covers custom tailored tutorials to help you develop , maintain and troubleshoot data movement processes and environments using Azure Data Factory V2 and SQL Server Integration Services 2017

BookMay 2018284 pages

Automated Machine Learning with Microsoft Azure

A practical, step-by-step guide to using Microsoft's AutoML technology on the Azure Machine Learning service for developers and data scientists working with the Python programming language

BookApr 2021340 pages

Cloud Analytics with Microsoft Azure

Cloud Analytics with Microsoft Azure is an end-to-end guide to processing and analyzing big data using a range of Microsoft Azure features. This book covers everything you need to build your own data warehouse and learn numerous techniques to gain useful insights by analyzing big data.

BookNov 2019242 pages

Cloud Analytics with Microsoft Azure

Cloud Analytics with Microsoft Azure enables you to understand the design and business considerations that you must keep in mind while planning to adopt the cloud analytics model for your business.

BookJan 2021184 pages

Azure Serverless Computing Cookbook

With the pace at which the cloud technology is progressing, developers can find it difficult to understand the different technical methods available to solve a business problem. This book will help you to understand how to develop native serverless applications in the native way and integrate them with other related services.

BookJun 2020458 pages

Cloud Scale Analytics with Azure Data Services

This book will help you to understand the architectural components of a modern data warehouse and select those suitable for your requirements. You’ll learn everything from how to integrate your source data into Azure Data Lake at scale to how to structure your analytical data estate and more.

BookJul 2021520 pages

Azure DevOps Explained

Azure DevOps is a cloud service–providing development and collaboration tool with version control, reporting, requirements management, project management, automated builds, lab management, testing, and release management capabilities. If you are new to the Azure DevOps platform, this comprehensive guide will have you up to speed in no time.

BookDec 2020438 pages5

SQL Server 2017 Integration Services Cookbook

SQL Server Integration Services is a tool that facilitates data extraction, consolidation, and loading options (ETL), SQL Server coding enhancements, data warehousing, and customizations. With the help of this book, you’ll gain complete hands-on experience of SSIS 2017’s new features, and design and development improvements including SCD, Profiling, Tuning, and Customizations.

BookJun 2017558 pages

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages