SQL/ETL Developer - T-SQL/Stored Procedures/ETL/SSIS

Develop an ETL solution using SSIS

3.75 (232 reviews)
Udemy
platform
English
language
Databases
category
SQL/ETL Developer - T-SQL/Stored Procedures/ETL/SSIS
4,100
students
6.5 hours
content
May 2023
last update
$64.99
regular price

What you will learn

Install Microsoft SQL Server

Install SQL SERVER Data Tools - SSDT

Install Visual Studio

Download and attach sample database

Aggregate Functions

Analytic Functions

Rank Functions

Extract data from multiple tables

Create stored procedures

Create a new SSIS project

Develop and implement ETL process with SSIS

Test and Run SSIS Package

Why take this course?

A common problem that organizations face is how to gathering data from multiple sources, in multiple formats, and move it to one or more data stores. The destination may not be the same type of data store as the source, and often the format is different, or the data needs to be shaped or cleaned before loading it into its final destination.

Extract, transform, and load (ETL) is a data pipeline used to collect data from various sources, transform the data according to business rules, and load it into a destination data store.

SQL Server Integration Services (SSIS) is a useful  and powerful Business Intelligence Tool . It is best suited to work with SQL Server Database . It is added to SQL Server Database when you install SQL Server Data Tools  (SSDT)which adds the Business Intelligence Templates to Visual studio that is used to create Integration projects.


SSIS can be used for:

  •  Data Integration

  •  Data Transformation

  •  Providing solutions to complex Business problems

  •  Updating data warehouses

  •  Cleaning data

  •  Mining data

  •  Managing SQL Server objects and data

  •  Extracting data from a variety of sources

  •  Loading data into one or several destinations

What You Will Learn ....

  •  How to install SQL Server Database

  •  How to download and attach a database to SQL Server

  •  How to download and install SQL Server Data Tools

  •  How to Create a New Integration Services Project

  •  How to add and Configuring a Flat File Connection Manager

  •  How to add and Configuring an OLE DB Connection Manager

  •  How to add a Data Flow Task to the Package

  •  How to add and Configuring the Flat File Source

  •  How to add and Configuring the Lookup Transformations

  •  How to Create Integration Services Tasks

  •  How to Create  New Connection Manager

  •  How to Write data to a SQL Server database

  •  How to Execute a package from SQL Server Data Tools

  •  How to Control Data Flow for Flat Files

  •  How to test  Packages 

  • SQL Functions

  • T-SQL Stored procedures

  • Extracting data from multiple tables





Content

Introduction

Introduction
What is SQL
What is ETL ?
What is T-SQL
What is SSIS
What is a Data Warehouse
What is visual studio

Setting Database System

Installing Visual Studio - Part 1
Installing Visual Studio - Part 2
Download MS SQL Server 2014 Express
Install MS SQL Server 2014 Express
Download Adventureworks sample database
Attach Sample Database
Install MS SQL Server 2017 Express
Download AdventureworksDW Database
Download and Install SSDT
What is SSMS
What is a Schema

T-SQL Functions

AVG Aggregate Function
MIN Aggregate Function
MAX Aggregate Function
SUM Aggregate Function
COUNT Aggregate Function
GROUPING Aggregate Functions
VAR Aggregate Function
VARP Aggregate Function
RANK Function
NTILE Function
ROW Number function
DENSE RANK Function
Analytic Function Syntax
LEAD Analytic Function
LAG Analytic Function
LAST_VALUE Analytic Function
First _Value Analytic Function
CUME_DIST Analytic Function
PERCENTILE_DISC Analytic Function

Extracting Data From Multiple Tables

Extracting data using an INNER Join
Extracting data using a LEFT Outer Join
Extracting data using a RIGHT OUTER Join
Extracting data using an FULL OUTER Join

T-SQL Stored Procedure

Introduction to stored procedures
Creating stored procedures
Modifying stored procedures
Properties page of a stored procedure
Viewing dependencies of a stored procedure
Deleting a stored procedure
Viewing definition of a stored procedure

CRUD Stored Procedures

What is CRUD
Naming Convention
Basic Application Design Structure
Stored Procedure For CREATE CRUD Operation
Stored Procedure For READ CRUD Operation
Stored Procedure For UPDATE CRUD Operation
Stored Procedure For DELETE CRUD Operation

Developing ETL with SSIS

Create a new SSIS project
Add a Flat File Connection Manager
Remap Column Data Types
Add and Configure OLE DB Connection Manager
Add a Data Flow Task
Add and configure Flat File Source
Add and Configure Lookup Transformation
Add and Configure Lookup For DateKey Transformation
Add and Configure OLE DB Destination
Test and Run Package

Our review

📚 **Course Overview and Rating:** The Global course rating stands at 3.75, with recent reviews providing a mix of opinions regarding the effectiveness and organization of the course on learning programming T-SQL Procedures and ETL (Extract, Transform, Load) processes using SQL Server Integration Services (SSIS). **Pros:** - The course provides a high-level overview of SQL server queries, stored procedures, and SSIS. (Multiple reviews) - Some instructors were clear and their knowledge transferred effectively to the students. (Several reviews) - A few participants appreciated the pace at which the instructor spoke, allowing them to keep up. - The course introduced new concepts that were helpful for further learning. **Cons:** - The course lacks a comprehensive resource page, making it difficult for students to find necessary files and documentation. (Multiple reviews) - The instruction often skipped over 'why' certain steps were taken, leaving students without a clear understanding of the underlying principles. (Several reviews) - Examples provided in the course were sometimes insufficient or not demonstrated effectively. (Several reviews) - Some instructors had a voice that was distracting or poorly recorded, impacting the learning experience. (Multiple reviews) - The SSIS portion of the course was deemed too high-level and lacked detailed explanations and real-world applications. (Several reviews) - A significant number of participants reported missing resources, such as sample files and clear definitions of ETL processes. (Multiple reviews) - There were concerns about the lack of engagement, with some students feeling the course jumped around arbitrarily without a clear progression. (Several reviews) - Some sections of the course ended abruptly without showing the final results or explaining the outcomes. (Multiple reviews) - The Q&A section was criticized for not answering student questions. (Multiple reviews) - The course installation instructions were outdated, and no updates were provided to students. (Multiple reviews) - The lack of critical thinking opportunities and the reliance on copying and pasting code without explanation were noted as negative aspects. (Multiple reviews) - A few students reported issues with the course not working as expected, particularly in the practical application of SSIS. (Several reviews) **Student Experiences:** - Some students expressed appreciation for the basics being covered and the foundational knowledge they gained. However, they also highlighted the need for additional resources and clearer explanations. - The absence of essential resources for practice was a recurring issue, with some students unable to complete certain sections due to missing files. - A few students mentioned that while they followed the steps provided, they were unclear about the 'why' behind those steps, indicating a need for more comprehensive instruction and explanations. **Recommendations:** - It is recommended that the course be updated to include a complete resource page with all necessary files and examples. - More detailed explanations, especially for complex syntax and concepts, should be provided, with an emphasis on demonstrating real-world applications. - The course should offer clearer explanations of 'why' certain steps are taken, ensuring that students understand the underlying principles and their practical significance. - The instructor's voice and recording quality need to be improved to enhance the learning experience. - It is advisable for students to verify the hardware requirements and SSIS configurations independently, as this information may change over time. - The course should be reviewed and revised to ensure that all sections work as intended and that the final results are demonstrated clearly. **Final Thoughts:** While some aspects of the course were well-received for their clarity and foundational approach, the consistent feedback regarding missing resources, insufficient explanations, and issues with the practical application of concepts suggest that the course could significantly benefit from revisions to improve the overall learning experience. Students are advised to consider these reviews when deciding whether this course aligns with their educational needs and goals.

Charts

Price

SQL/ETL Developer - T-SQL/Stored Procedures/ETL/SSIS - Price chart

Rating

SQL/ETL Developer - T-SQL/Stored Procedures/ETL/SSIS - Ratings chart

Enrollment distribution

SQL/ETL Developer - T-SQL/Stored Procedures/ETL/SSIS - Distribution chart

Related Topics

2382211
udemy ID
5/24/2019
course created date
5/26/2019
course indexed date
Bot
course submited by