Data integration with SSIS - with SSAS and Power BI bonus

Name: Data integration with SSIS - with SSAS and Power BI bonus
Rating: 3.8 (18 reviews)

Learn SQL Server Integration Services with a real Data Warehouse Project plus SSAS and Power BI bonuses

Created byExpert-Only Training

Last updated 5/2026

English

What you'll learn

Install SQL Server, and SSIS
Check and run SQL Server services on Windows
Install Visual Studio and SSIS Projects
Install SSMS
Create SSIS projects and solutions with Visual Studio
Create your first SSIS package from scratch
Learn how to connect to SQL Server databases
Import and export CSV, Excel files and XML documents
Learn how to manage errors during data integration
Zip and Unzip Windows files and folders with SSIS
Import and export multiple files at once with dynamic SSIS packages
Learn best practice for integrating data using files
Install SSAS
Develop a simple Tabular Model
Install Power BI
Develop a simple Power BI report with graphs
Install SSAS Projects on Visual Studio
Understand High Level ETL and BI concepts

Course content

10 sections • 58 lectures • 10h 46m total length

Overview of the course9:56
Introduction to BI and SSIS15:32
Test your knowledge on BI , ETL and SSIS concepts
Download and install SQL Server 202216:17
Download SQL Server 2022 developer edition
Install SQL Server and SSIS with default options
Restart the Windows PC or Server
Start / Stop the services
Download and install SSMS 199:58
Download and install Visual Studio 2022 and SSIS10:41
The MS BI development environment quiz
Create an SSIS solution, project and package in Visual Studio7:03
Learn to set up your first SSIS solution and project in Visual Studio 2019 in this lecture.

Understand the role of Visual Studio as Microsoft's main development environment for SSIS.
Solutions in Visual Studio are containers for SSIS projects.
SSIS Projects, in turn, group SSIS packages.
Grouping by themes or functional areas makes organization easier.
Pre-requisites
Ensure you've installed Visual Studio and the Integration Services project extension first.

Quick Steps:
Launch Visual Studio 2019.
Choose Create a new project.
Search and select the Integration Services Project template.

You are now ready to dive in to create your first package to import data into SQL Server.
Key learnings
In Visual Studio and Microsoft BI generally, objects are organized this way:
Solutions
contains SSIS Projects (or SSRS / SSAS)
contains SSIS packages (or reports or cubes)
Technically, SSIS packages are XML files with the a .dtsx extension.
P01 : Develop a package to copy and transform data12:53
SSIS is the ETL brick of the SQL Server BI suite and the SSIS packages are used to integrate and transform data.

ETL stands for Extract, Transform and Load.

In an SSIS package, the basic elements are control flows, data flows, source, destinations and transformation components. In addition, data integration software suites are at the heart of data warehouse development projects.

Formula used in the lecture
The formula used in the package retrieves the first letter of the first name, then concatenates the result with the first letter of the last name and adds a point after each letter:

LEFT([Derived- trim].FirstName,1) + "." + LEFT([Derived- trim].LastName,1) + "."

Key learnings

An SSIS package is structured as follows:
SSIS packages (Package.dtsx for example)
contains one or multiple Control Flows
contains one or multiple Data Flows that uses
Source components (files, tables, web services, etc.)
Transformations (aggregations, derived columns, cast, enrichments, etc.)
Target components (files, tables, web services, etc.)
Needs Connection Managers to connect to source and target elements.
SSIS projects and package structure

Flat files definition and key points5:53
P02 : Import a Text file into a SQL Server table14:37
Change the encoding of the source file in SSIS until the data is displayed correctly.
To find the encoding of the file, simply use Notepad++ and open the file. There are 2 possibilities to check the encoding:
From the Encoding menu
At the bottom right of the software where information about the file is displayed.
Double check the encoding of a text file easily with Notepad++
To load the text file without errors, change the encoding and use 65001 (UTF-8) instead of 1252 (ANSI – Latin I) proposed by default by SSIS.
Change the file encoded to 65001 (UTF-8) on the code page setting

Key learnings

Download and use Notepad++ here : https://notepad-plus-plus.org/ to check text files content and encoding
Copy and paste packages inside projects to quickly duplicate them
You can Copy elements from a package to another: Connections, Data Flows
Structure the table to fit exactly the file structure
Same column names
Compatible data types, especially text columns lengths
Check the Code Page of the file to load to avoid wrong characters
P03 : Export data from SQL Server to CSV files14:15
Develop an SSIS Package to export the content of the Customers table with 24 lines into a CSV file.

Key Learnings

Use a sample file with header and a few lines so SSIS detects automatically the structure.
Flat files import and export
P04 : Import multiple CSV files at once with a dynamic loop18:20

Import and export Excel files6:27
P05 : Install and test Excel Drivers12:21
Install and configure the Excel Drivers to connect to XLSX files from SSIS.
Key Learnings
In this lecture 4 steps:
Check the drivers installed on your PC and download the drivers installation programs
Install the 64 bits version of the Access Database Engine
Install the 32 bits version of the Access Database Engine in quiet mode
Test the drivers by reading of Excel data from SSIS
P06 : Export data from SQL Server to an Excel file13:37
Test your Excel integration knowledge
P07 : Manage Excel Sheets and Data Types in exports15:40
P08 : Import specific Excel cells and filter Excel data with SSIS19:28

BI Project Presentation10:57
Step 1: Create ODS and DWH databases
Step 2: Create ODS tables and load static dimensions
Step 3: Load the Fuel Prices in the ODS
Step 3 solution Part 1: Solution, Package and flat file connection5:25
Step 3 solution Part 2: Sequence, Loop and Data Flow8:58
Step 3 solution Part 3: Duplicate Work for Fuel Prices Without Taxes6:55
Step 4: Load Exchange Rates in the ODS
About the DWH Database Structure5:40
Step 5: Create the DWH tables
Step 6: Load DWH Dimension tables
Step 6 solution Part 1: Create DWH Time dimension data flow10:13
Step 6 solution Part 2: Create DWH Currency and Country dimension data flows4:32
Step 7: Develop Fact Fuel Prices DWH Package
Step 8: Develop Exchange Rates DWH Package

Bonus Step Part 1: Install Analysis Services (SSAS)7:55
Bonus Step Part 2: Install Analysis Services Projects on Visual Studio6:26
Bonus Step Part 3: Import DWH tables and create relationships in SSAS16:47
Bonus Step Part 4: Create DAX Measures and Hierarchies11:29
Bonus Step Part 5: Enrich Exchange Rates and Analyse DWH in Excel8:31
Bonus Step Part 6: Deploy SSAS Tabular Model or Import it using XMLA7:53
Bonus Step Part 7: Fine Tuning the SSAS Model8:20
Bonus Step Part 8: Download and Install Power BI Desktop4:45
Bonus Step Part 9: Create a Power BI Report with Graphs15:33
10 Expert SSIS Tips for Successful BI Projects14:08
A Final Word2:24

Requirements

All required Software installed during the training
Experience of T-SQL is preferable, but not mandatory (all SQL queries are provided).

Description

Unlock the Power of Data Integration with Our SSIS Course!

Enroll today on a journey directly into the realm of SQL Server Integration Services (SSIS) with our specially crafted course that is for both novices and more experienced IT professionals.

This course is Designed by me, a seasoned Microsoft BI and EPM Consultant with over 15 years of hands-on experience.

It is a quick into mastering the essentials of SSIS, so you handle data integration tasks with confidence.

Key Highlights

Seamless Setup and Configuration
- Install SQL Server, SSIS, SSAS, SSMS, Visual Studio and Power BI
- Check and manage SQL Server Services on Windows
Hands-On Project Development
- Create SSIS projects and solutions from scratch using Visual Studio
- Develop your first SSIS package explained step-by-step
- Import and export CSV, Excel files, and XML documents with expert tips
- Zip and Unzip Windows files and folders effortlessly with SSIS and 7zip
- Pivot and Unpivot datasets
- One Script example inside SSIS
No Prior SQL Experience Required : All SQL queries are provided
- T-SQL familiarity is preferred but not mandatory
- Beginners are welcome – you can start your SSIS journey from scratch
Advanced Techniques for Professionals - by an SSIS Expert
- Import and export multiple files simultaneously with dynamic SSIS packages
- Manage errors and logs during data integration effectively
Versatile Audience
- Tailored for both Experienced and Junior BI Developers, Data Engineers, but also Senior Consultants
- Perfect for those new to SSIS or seeking a hans on training
Progressive BI Learning Path
- Includes an introduction of High-Level ETL and BI concepts
- Introduction to the star schema model
- Learn best practices for integrating data using files and SQL Server
- Increasing complexity
Practical end-to-end Real Life BI Project
- Create the data model
- Load data from files to Staging and then in a Datawarehouse
- Create a Tabular Model in SSAS
- Develop a Power BI Dashboard

By the end of this course, you'll have a good overview and practical experience with SSIS. And also with SQL Server, Analysis Services tabular model and Power BI, enabling you to confidently create SSIS projects.

You will be able to manage data integration challenges, and contribute to the success of your organization's BI initiatives.

Start your SSIS journey today!

Who this course is for:

You're new to SSIS or just want to understand how it works and especially how to get started.
You are a beginner or with minimal SQL Server or T-SQL experience. It will help, but it is not necessary.
This course is suitable for you if you are beginning from scratch or if you know previous versions of SSIS.
It is also for Experienced or Junior Developers, Data engineers or Consultants who wants to quickly learn SSIS basics and best practices.

Data integration with SSIS - with SSAS and Power BI bonus

What you'll learn

Explore related topics

Course content

Introduction to SSIS and environment set-up7 lectures • 1hr 22min

Import and export Flat Files (CSV and TXT)4 lectures • 53min

Import and Export Excel files5 lectures • 1hr 8min

Import and export XML documents4 lectures • 54min

Manage Logging, Metadata, Process and Errors5 lectures • 1hr 4min

Variables, Configurations and Deployments6 lectures • 1hr 3min

Data Transformations5 lectures • 53min

Zip and Unzip files and folders with SSIS4 lectures • 30min

Real End To End BI Project7 lectures • 53min

Bonuses and final word11 lectures • 1hr 44min

Requirements

Description

Who this course is for: