Book Image

Mastering SQL Server 2017

By : Miloš Radivojević, Dejan Sarka, William Durkin, Christian Cote, Matija Lah
Book Image

Mastering SQL Server 2017

By: Miloš Radivojević, Dejan Sarka, William Durkin, Christian Cote, Matija Lah

Overview of this book

Microsoft SQL Server 2017 uses the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. By learning how to use the features of SQL Server 2017 effectively, you can build scalable apps and easily perform data integration and transformation. You’ll start by brushing up on the features of SQL Server 2017. This Learning Path will then demonstrate how you can use Query Store, columnstore indexes, and In-Memory OLTP in your apps. You'll also learn to integrate Python code in SQL Server and graph database implementations for development and testing. Next, you'll get up to speed with designing and building SQL Server Integration Services (SSIS) data warehouse packages using SQL server data tools. Toward the concluding chapters, you’ll discover how to develop SSIS packages designed to maintain a data warehouse using the data flow and other control flow tasks. By the end of this Learning Path, you'll be equipped with the skills you need to design efficient, high-performance database applications with confidence. This Learning Path includes content from the following Packt books: SQL Server 2017 Developer's Guide by Miloš Radivojevi?, Dejan Sarka, et. al SQL Server 2017 Integration Services Cookbook by Christian Cote, Dejan Sarka, et. al

Related Content you might be interested in

Current Title:

Small book image
Mastering SQL Server 2017
Miloš Radivojević | Dejan Sarka | William Durkin | Christian Cote | Matija Lah
Aug, 2019 | 11 hours 36 minutes
No titles found
Table of Contents (20 chapters)
Title Page
Title Page
Copyright
Copyright
Mastering SQL Server 2017
Contributors
Contributors
About the Authors
Packt Is Searching for Authors Like You
About Packt
About Packt
Why Subscribe?
Packt.com
Preface
Preface
Who This Book Is For
What This Book Covers
To Get the Most out of This Book
Get in Touch
Free Chapter
1
Introduction to SQL Server 2017
Introduction to SQL Server 2017
Security
Summary
2
SQL Server Tools
SQL Server Tools
Installing and updating SQL Server Tools
New SSMS features and enhancements
SQL Server Data Tools
Tools for developing R and Python code
Summary
3
JSON Support in SQL Server
JSON Support in SQL Server
Why JSON?
What is JSON?
JSON in SQL Server prior to SQL Server 2016
Retrieving SQL Server data in JSON format
Converting JSON data in a tabular format
JSON storage in SQL Server 2017
Validating JSON data
Extracting values from a JSON text
Modifying JSON data
Performance considerations
Summary
4
Stretch Database
Stretch Database
Stretch DB architecture
Limitations of using Stretch Database
Use cases for Stretch Database
Enabling Stretch Database
Querying stretch databases
SQL Server Stretch Database pricing
Stretch DB management and troubleshooting
Summary
5
Temporal Tables
Temporal Tables
What is temporal data?
System-versioned temporal tables in SQL Server 2017
What is missing in SQL Server 2017?
Summary
6
Columnstore Indexes
Columnstore Indexes
Analytical queries in SQL Server
Columnar storage and batch processing
Nonclustered columnstore indexes
Clustered columnstore indexes
Summary
7
SSIS Setup
SSIS Setup
Introduction
SQL Server 2016 download
Installing JRE for PolyBase
Installing SQL Server 2016
SQL Server Management Studio installation
SQL Server Data Tools installation
Testing SQL Server connectivity
8
What Is New in SSIS 2016
What Is New in SSIS 2016
Introduction
Creating SSIS Catalog
Custom logging
Azure tasks and transforms
Incremental package deployment
Multiple version support
Error column name
Control Flow templates
9
Key Components of a Modern ETL Solution
Key Components of a Modern ETL Solution
Introduction
Installing the sample solution
Deploying the source database with its data
Deploying the target database
SSIS projects
Framework calls in EP_Staging.dtsx
10
Dealing with Data Quality
Dealing with Data Quality
Introduction
Profiling data with SSIS
Creating a DQS knowledge base
Data cleansing with DQS
Creating a MDS model
Matching with DQS
Using SSIS fuzzy components
11
Unleash the Power of SSIS Script Task and Component
Unleash the Power of SSIS Script Task and Component
Introduction
Using variables in SSIS Script task
Execute complex filesystem operations with the Script task
Reading data profiling XML results with the Script task
Correcting data with the Script component
Validating data using regular expressions in a Script component
Using the Script component as a source
Using the Script component as a destination
12
On-Premises and Azure Big Data Integration
On-Premises and Azure Big Data Integration
Introduction
Azure Blob storage data management
Installing a Hortonworks cluster
Copying data to an on-premises cluster
Using Hive – creating a database
Transforming the data with Hive
Transferring data between Hadoop and Azure
Leveraging a HDInsight big data cluster
Managing data with Pig Latin
Importing Azure Blob storage data
13
Extending SSIS Custom Tasks and Transformations
Extending SSIS Custom Tasks and Transformations
Introduction
Designing a custom task
Designing a custom transformation
Managing custom component versions
14
Scale Out with SSIS 2017
Scale Out with SSIS 2017
Introduction
SQL Server 2017 download and setup
SQL Server client tools setup
Configuring SSIS for scale out executions
Executing a package using scale out functionality
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Correcting data with the Script component

In this recipe, you will use the Script Component in the data flow as a transformation for advanced data cleansing. You will read an Excel file and do a custom transformation in order to make the output ready for further processing in the data flow.

Getting ready

In order to test this recipe, you need to have an Excel file prepared. In the file, there should be a single sheet with the following content:

For your convenience, an Excel file with the content needed is provided in the Ch07_Orders.xls file.

Note that the table represents simple orders with order details. However, the order info is added to the first order details line only. Your task is to add the appropriate order info to every...

Previous Section
End of Section 6
Next Section