SQL Server Integration Services (SSIS)

Introduction

Hello, welcome to SSIS course. My name is Miraz Nabi and I will be your instructor for this course.
This is an advanced level database course and it requires the knowledge of databases.

Before starting this course, I would recommend to complete Introduction to Database and
Introduction to Database Administration courses. These two courses will help you to understand SSIS
course as well as SSAS and SSRS course.

If you have already completed these two courses, I believe this SSIS course will be easier for you.

What is SSIS?

SSIS stands for “SQL Server Integration Services“. It is a platform to build business-level data integration and data transformations solutions. It solves complicated business problems by copying or downloading data, loading into data warehouses, mining data and managing data.

The SSIS extracts and transforms data from various sources such as xml data files, flat files and load
the data into the destinations.

You can use SSIS graphical tools to develop solutions without writing a single line of code.

We will also cover in this course:

-Installation Microsoft SQL Server 2017
-Restore Sample Database
-Create DW in MS SQL Server
-Create Stage Area Table
-Implementation of data flow

Software Installation

In this video, we will cover how to install softwares that are needed for this course.

First, we need SQL Server 2017 to be downloaded.

Go to the below link to download SQL SERVER 2017

Link: Try SQL Server 2017 RTM on Microsoft Evaluation Center

Go to the below link to download SQL Server Data Tools (SSDT) for Visual Studio

Link: Download SQL Server Data Tools (SSDT) – SQL Server Data Tools (SSDT) | Microsoft Docs

Go to the below link to download SQL Server Management Studio (SSMS)

Link: Download SQL Server Management Studio (SSMS) – SQL Server Management Studio (SSMS) | Microsoft Docs

Setting Up / Sample Database Upload

After we’ve finished downloading and installing the required software, we’ll need to download sample databases from Microsoft.

In this video, we will demonstrate how to download AdvantureWorks2017 sample databases and then upload them to MS SQL Server so that we can play with them later.

Resources:

AdventureWorks sample databases – SQL Server | Microsoft Docs

Creating A Data Warehouse

In this video, we will learn how to create a Data Warehouse. Data warehousing is a technology that collects structured data from one or even more sources and compares and analyzes it for improved data analytics.

In this video, we will also show how to how to create a RetailDW then upload sample files to the data warehouse.

Download RetailDW data files from here: https://www.dropbox.com/sh/gyc1wpvj8cumhhl/AAATWjnhRpi1T9erFL2dQr0na?dl=0

Create new database.
Open SQL Server Management Studio(SSMS) > Connect to SQL Server > Expand Databases > Select Databases > Click the right button > Select new Databases > Write the database name > Click the 3-dot button in owner option > opt in [sa] and your desktop/laptop name > click ok.

Upload XLS data file in the database.
Select newly created database > Click the right button > Select tasks > Select Import Data > Select data source “Microsoft Excel” > browse to select the file > Click open > Select Excel version 97-2003 > Click Next button > Select Destination “SQL Server Native Client 11.0” > Select database > then continue to finish the process.

Upload CSV data file in the database.
Select newly created database > Click the right button > Select tasks > Select Import Flat File > > browse to select the file > Click next button > in Modify Columns, change Data Type option as shown in the video > Click next to finish the process.

Creating a Staging Area

A staging area is primarily required in a Data Warehousing Architecture for timing reasons. In short, before data can be integrated into the Data Warehouse, all required data must be available.

Create a Stage Area Table.

Open SQL Server Management Studio(SSMS) > Connect to SQL Server > Expand Databases > Select RetailDW or (newly created database) > Click New Query button

Data Flow Implementation

Download ResellerSales file from here.

Create an Integration Services Projects.
Open Visual Studio 2017 (SSDT) > Click file > Click new > Click project > Choose ‘Integration Services’ from the left side options > Select “Integration Services Project” in the middle options > Write a name in the Name option bar > Click Ok.

Upload flat data file in the Visual Studio.
Select and drag ‘Data Flow Task’ from left side to the middle > Double click the ‘Data flow Task’ > Select and drag ‘Source Assistant’ from left side to the middle > Double click the ‘Source Assistant’ > Select ‘Flat File’ from left side and select New from right side > Click ok > Browse to select the file > Click ok.

Upload the data in the database.
Select and drag ‘Destination Assistant’ from the left side to the middle > Double click the ‘Destination Assistant’ > Select ‘SQL Server’ from left side and select New from right side > Click ok > Select Provider “SQL Server Native Client 11.0” > Click drop down button from Server name option > Select SQL Server (which is MSSQLSERVER2017) > Click drop down button from ‘Select or enter a database name’ > Select AdventureWorksDW2017 > Click ok.

Connect Source to Destination
Click ‘Flat File Source’ > Select and drag blue arrow to connect ‘OLE DB Destination’ > Double click ‘OLE DB Destination’ > Click the drop down button from ‘Name of the table or the view’ > Select “FactResellerSales” table > Click ok.

Setup the SQL execution
Go back to ‘Control Flow’ > Select and drag ‘Execute SQL Task’ from left side to the middle > Double click ‘Execute SQL Task’ > Click on the right side of the ‘Connection’ > Select SQL Server (which is MSSQLSERVER2017) > Click on the right side of the ‘SQLStatement’ > Write “TRUNCATE TABLE [dbo].[FactResellerSales]” > Click ok > Select and drag the green arrow to connect ‘Data Flow Task’ > click ‘Start’ button.

Fix the Error
Go back to ‘Data Flow’ > Select ‘Flat File Source’ > Click right button of the mouse > Select ‘Show Advanced Editor’ > go to ‘Input and Output Properties’ > Expand ‘Flat File Source Output’ > Expand ‘Output Columns’ > Continue to fix the errors.

Use Data Conversion
Select and drag ‘Data Conversion’ from left side to the middle > Click ‘Flat File Source’ > Select and drag blue arrow to connect ‘Data Conversion’ > Click ‘Data Conversion’ > Select and drag blue arrow to connect ‘OLE DB Destination’ > Click Start.

Conclusion

Thank you for taking the Introduction to SSIS course. In the future, we will offer a more in-depth course that will cover a lot more. To learn more about SSIS, please see the book listed below.

SQL Server Integration Services (SSIS)