Name: Microsoft SSIS Real-time Scenarios (Package Design)
Rating: 3.4 (31 reviews)

Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Created byOm Shakya

Last updated 8/2022

English

What you'll learn

Important concepts & features of SSIS & their use cases
Understanding & implementing real-time scenarios in SSIS
How to tackle and handle real-time business requirements to create SSIS packages
Important tips & tricks of Expressions, ADO Net & OLE DB connection managers & other paramount features
How to execute Python & R scripts, executing different types of SQL Stored procedures and more
More than 35 real-time SSIS scenarios implementations

Course content

11 sections • 52 lectures • 6h 4m total length

Introduction3:00
Overview of all modules in the course1:53

Overview of Module 2: Executing SQL Stored Procedures1:22
Scenario 1: Calling stored procedure without parameters4:57
Scenario 2: Calling stored procedure with parameters that returns a scalar value6:41
Scenario 3: Calling stored procedure with parameters that returns a dataset8:33
Demonstrate calling a stored procedure with parameters in ssis, returning a multi-row result set. Consume the data with a for each loop and script task to format and display results.

Overview of Module 3: Implementing SSIS flexibility using Expressions4:44
Scenario 1: Exp - Controlling precedence constraints (Logical conditioning)6:49
Scenario 2: Exp - Creating a dynamic SQL statement6:21
Demonstrates creating dynamic SQL statements in SSIS using expressions and variables to build and execute queries that return random salesperson data.
Scenario 3: Exp - Extracting path or filename from full path4:05
Extract the file name and its part from a full path using SSIS variables and expressions, demonstrating replacing the name with blank and using the right function to isolate filename.
Scenario 4: Exp (Way-1) - Separate numeric & string values of a column5:16
Scenario 4: Exp (Way-2) - Separate numeric & string values of a column5:43
Conclusion Module 3: Implementing SSIS flexibility using Expressions1:28

Overview Module 5: Extending SSIS functionalities using Scripts (C# or VB.Net)3:49
Scenario 1: Script Task - Zip and Unzip compressed files6:30
Zip a folder using a script task to create a compressed file and unzip to an output folder, producing date-stamped vendor folders with the .net compression library.
Scenario 2: Script Task - Get list of specific files from multilevel directory9:12
Scenario 3: Script Task - Load a delimited file into SQL9:05
Scenario 4: Script Task - Download file from http(s) URL4:50
Scenario 5: Script Task - Convert a JSON file into csv10:20
Scenario 6: Script Task - Validate header (HDR) and trailer (TLR) of a file12:05
Explore script task validation of a file’s header and trailer in SSIS for real-time scenarios, ensuring the header defines columns and the trailer confirms record counts before loading data.
Conclusion Module 5: Extending SSIS functionalities using Scripts (C# or VB.Net)1:38

Overview of Module 7: Loading delimited flat files into SQL database tables2:35
Scenario 1: Flat file - loading a CSV file13:54
Scenario 2: Flat file - loading a TSV file6:10
Scenario 3: Flat file - loading a PSV file4:28
Scenario 4: Flat file - loading a SSIS Raw file6:29
Learn to load a proprietary flat file into a database using SSIS raw file source and destination, with built-in data types and no parsing or transformation.

Overview of Module 8: Loading data, Incremental Load & SCD-2 + more6:49
Scenario 1: Extracting numeric from a alphanumeric string values of a column6:52
Scenario 1 demonstrates extracting numbers from alphanumeric values in a data column using a SSIS dataflow task, then loading the numeric results to the destination.
Scenario 2: Generic ETL (load a table with load date into destination)7:58
Explore a generic ETL workflow in SSIS: extract data from any source, apply data conversion, sort and deduplicate, then map and load into any destination.
Scenario 3: Flush and load or truncate and load, full load every time5:36
Scenario 4 (Part-1): Merging data from multiple sources and loading into table9:00
Merge data from multiple sources, including database and flat file, using sort and merge join transformations and left outer joins, then load into the destination table.
Scenario 4 (Part-2): Merging data from multiple sources and loading into table1:35
Scenario 5: (Way-1) Pivoting the data and load into a table3:54
Pivot the data into a metrics form by turning years 2019 and 2020 into columns, summing sales amount by salesperson, and loading the results into the destination table.
Scenario 5: (Way-2) Pivoting the data and load into a table7:26
Scenario 6: Implementing slowly changing dimension (SCD) type 232:08
Scenario 7.1.1: Implementing incremental/delta data load (ADO NET Connection)25:30
Scenario 7.1.2: Implementing incremental/delta data load (ADO NET Connection)9:24
Scenario 7.2: Implementing incremental/delta data load (OLE DB Connection)18:05
Scenario 8: - Import data from table into flat file with transformation13:55

Requirements

Basic understanding of SSIS (SQL Server Integration Services)
Basic understanding on designing SSIS packages
Basic understanding of SQL queries
And your presence is much required

Description

Welcome to this course Microsoft SSIS (SQL Server Integration Services) real-time scenarios implementation.

SCENARIOS WITH IMPLEMENTATION & DEMONSTRATION

In this course you will learn about the important concepts, features of SSIS; their use cases and how to tackle & handle the real-time or real-world business requirements and how we can implement these real-time scenarios in SSIS.

This course is for SSIS or ETL or SQL or Data Analyst or Data Engineers at any level (Beginner, Intermediate, Experienced) who uses or going to use the Microsoft SSIS (Microsoft SQL Server Integration Services) to work upon Data Integration/Migration or Data Warehousing projects and ETL (Extract-Transform-Load) projects.

You will learn concepts, tricks, tips about the useful and important tasks/transformations and the methods of using them intelligently and how to handle and implement real-time SSIS scenarios.

This course is designed after a great demand from the developers through various sources such as LinkedIn, blogs etc..

This course is the unique in terms of content to cover real-time scenarios in SSIS and the only course available and published first time to the great audience and learners, if that kind of course is available in the market then it would cost 3 or 4 times of the cost of this course. Behind the scene this course is taught to 50+ developers via in-person training by the instructor.

In this course we will cover around 40 scenarios and their implementations silently to the point in short videos without boring you.

This is not intended for any related Microsoft Exams, it is about learning and implementing in actual projects on job to retain your position in your current organization and survive as a SSIS/ETL Developer.

This course is divided into various modules and covers 35+ real-time scenarios, tips & tricks. The scenarios in this course are based on the following high level topics/features/concepts,

Modules [For details of scenarios in each topic see the course content]

• Module 1: Using Derived Column transformation

• Module 2: Executing SQL Stored Procedures

• Module 3: Implementing SSIS flexibility using Expressions

• Module 4: Using Import Column & Export Column transformations intelligently

• Module 5: Extending SSIS functionalities using Scripts (C# or VB dot Net)

• Module 6: Using SSIS FOREACH & FOR LOOP Containers

• Module 7: Loading delimited flat files (csv, tsv, psv) & SSIS Raw file into SQL database tables

• Module 8: Loading data, Incremental Load & SCD-2

• Module 9: Implementing Package restart ability using Check points + Using Execute Process Task

After finishing this course you will experience something energetic vibes and feel confident that you can implement any real-time scenario in SSIS,

Who this course is for:

Beginner, Intermediate SSIS/ETL (SQL Server Integration Services)/ETL Developer
Beginner, Intermediate SQL Developer or DBA
Experienced SSIS/ETL Developer seeking real-time scenarios implementations
Experienced SQL Developer or DBA seeking real-time scenarios implementations in SSIS
MSBI (Microsoft Business Intelligence), BI Developer seeking real-time scenario experience in SSIS
Report Analyst, Visualization Developer, Power BI Developer, Tableau/Qlik/Looker/MicroStrategy Report & Dashboard Developers

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 5min

Module 1: Using Derived Column transformation3 lectures • 12min

Module 2: Executing SQL Stored Procedures4 lectures • 22min

Module 3: Implementing SSIS flexibility using Expressions7 lectures • 34min

Module 4: Using Import Column & Export Column transformations3 lectures • 12min

Module 5: Extending SSIS functionalities using Scripts (C# or VB.Net)8 lectures • 57min

Module 6: Using SSIS FOREACH & FOR LOOP Containers3 lectures • 22min

Module 7: Loading delimited flat files into SQL database tables5 lectures • 34min

Module 8: Loading data, Incremental Load & SCD-2 + more13 lectures • 2hr 28min

Module 9: Package restart ability using Check point + Using Execute Process Task3 lectures • 17min

Requirements

Description

Who this course is for: