Learn data integration and development of SSIS packages with our SQL Server Integration Services online training.
Master data integration and ETL by following our SSIS Online Training, indeed this SQL Server Integration Services video full course is made for beginners. Discover the power of Microsoft’s leading ETL tool, through our in-depth SSIS lectures, with practical examples and SQL code.
Learn the essentials of creating and utilizing DTSX packages for various data operations, such as loading flat files, Excel files, XML files, and transferring data between databases using DTSX packages. And also exporting data and managing errors.
What is SQL Server Integration Services ?
SQL Server Integration Services abbreviated as SSIS is a data loading tool that can be used to copy and transform data from one location to another. SSIS is a very powerful and flexible tool that can be used for many different purposes. Its main purpose is to load data into databases, or to transfer it from one data source to another. Here we use SQL Server database and tables as targets. It can also extract, transform and load data from other sources such as flat files (CSV files, Excel files, etc), but also different database vendors using specific connectors, like Oracle, Salesforce or SAP HANA for example.
It can also be used for tasks such as backing up data, copying data from one database to another or archiving logs. Technically, it is a feature of SQL Server that helps automate the process of moving data from one system to another.
Learn practical SSIS use cases in this Online Video Training
The courses are based on SQL Server 2019 and are gradually updated to the latest versions of MS SQL, i.e. SQL Server 2022. The current version is Microsoft SQL Server 2022. At Expert-Only.com, we offer top-quality IT courses for both beginners and seasoned professionals, including our 100% online SSIS training. Enhance your business intelligence skills with Microsoft BI tools like SSIS, SSAS, SSRS, and Power BI, and stay ahead in the ever-evolving IT industry.
Table of Contents
1. Introduction and set up SSIS development environment
First and foremost, to learn about data integration and develop your first project, start by installing the Microsoft SQL Server BI development environment. Here are the 3 necessary software packages:
1.1 Set up Integration Services development Environment on Windows
Indeed the first part of the introduction section is all about the development setup, necessary step to data management and integration. All software’s are free, you can download them on your Windows desktop, laptop or Server. Check the minimum hardware and software requirements to be sure they are met by your machine specs.
- Install the SSIS projects extension in Visual Studio 2022
- Download and install SQL Server Management Studio 18 (SSMS)
- Download and install SQL Server 2019 Developer Edition
In our SSIS Video Training, we learn how to download, install and use the latest versions available of the Microsoft BI stack:
- Download, install and launch SQL Server 2022 services
- Then Download and install SSMS 19
- And download and install Visual Studio 2022 and also the SSIS projects extension
1.2 Create basic SSIS packages
Then we learn SSIS package basics, create a solution, a project and a package with simple control flows and data flows. The main elements of a package are control flows and data flows. Typically having one or more sources and one or multiple targets.
- Create an SSIS project and package with Visual Studio 2019
- Connect to a SQL Server database using SSIS the connection manager
- Create a simple SSIS data flow to transfer and transform data
2. SSIS Training on how to import and export flat files
In this section, these SSIS courses are about flat file management in BI and DWH field (CSV or TXT for example). And especially the import of data from an external source to a database. In these examples, the target database is a MS SQL database.
- Flat files definition and integration key points (including common errors)
- Import a CSV or TXT file into a SQL Server table
- Export data from a SQL Server table into a CSV file using SSIS
- Create an SSIS loop to load multiple flat files with one data flow
3. SSIS video lectures to import and export Excel files
- Introduction to Excel data integration with SSIS
- Download and install the 2016 Excel drivers for SSIS
- Export data from a SQL Server table into an Excel file
- Manage the Excel columns data type detection with SSIS
- Import an Excel file into a SQL Server table with SSIS
4. SSIS training videos to import and export XML documents
This SQL Server Integration Services training explains step by step how to deal with XML documents for import and export.
- XML introduction and how to manage it with SSIS
- Import XML files into a SQL Server table using SSIS
- Export data from a SQL Server table into an XML document
- Export SQL Server XML data into an XML document with SSIS
5. How to manage SSIS logs, metadata, processes and errors?
- SSIS Video Training introduction to Logs, Metadata, Process and Errors
- Activate and read SQL Server logging (including SQL Server provider and XML)
- Activate SSIS log using XML dynamic file names
- Staging (ODS) to Data warehouse (DWH) process with custom metadata
- Video lecture to manage SSIS errors in packages
6. SSIS training lectures on variables, configurations and deployments
- About SSIS variables, configurations and SSIS deployments
- SSIS Variables lectures in 2 parts
- How to update Visual Studio and the SSIS Projects extension?
- Create SSIS configurations and deploy packages to SQL Server
- Create and manage different environments from the SSIS Catalog
7. Use data transformations in SSIS
- Data transformations presentation video
- Use the SSIS Merge JOIN component to group multiple tables
- Group multiple tables with SSIS Union All
- Use the SSIS Pivot transformation to transform rows to columns
- Transform columns into rows with SSIS UNPIVOT
- Aggregate data from multiple tables using SSIS
- Count distinct rows with SSIS
8. ZIP and Unzip files and folders with SSIS
In this Integration Services training, you’ll also learn in a very practical way how to compress into archives, and extract files and folders from compressed files using SSIS without using any third party software or licence.
- About Zipping and Unzipping using SSIS packages
- Download, Install and test the Zipping Software
- Zip files and folders into archives
- Unzip documents to extract files and folders
9. End-to-End BI project with SSIS using real life data
- The end to end BI Project Presentation
- Step 1: Create the ODS and the DWH databases
- Step 2: Create the ODS tables and load all the static dimensions
- Step 3: Load the Fuel Prices in the ODS tables
- Step 3 solution
- Part 1: Solution, Package and flat file connection
- Part 2: Sequence, Loop and Data Flow
- Part 3: Duplicate Work for Fuel Prices Without Taxes
- Step 4: Load Exchange Rates in the ODS
- About the DWH Database Structure
- Step 5: Create the DWH tables
- Step 6: Load the DWH Dimension tables using an SSIS package
- Part 1: Create DWH Time dimension data flow
- Part 2: Create DWH Currency and Country dimension data flows
- Step 7: Develop Fact Fuel Prices DWH Package
- Step 8: Develop Exchange Rates DWH Package
10. Training bonuses: SSAS Tabular and Power BI
- Install Analysis Services 2022 on Windows
- Install Analysis Services Projects on Visual Studio 2022
- Create the SSAS Tabular model, Import DWH tables and create relationships
- Create DAX Measures and Hierarchies
- Enrich Exchange Rates and Analyse the DWH data in Excel
- Deploy the Tabular Model or Import it using XMLA
- Fine Tuning the Tabular Model
- Download and Install Power BI Desktop
- Create a Power BI Report with Graphs
In the last 2 lectures, you’ll learn 10 tips based on real BI projects from an experienced BI and EPM consultant.
- 10 Expert SSIS Tips for Successful BI Projects
- A Final Word
About the SSIS Video Training
So, in conclusion to this Integration Services full training, SQL Server Integration Services is a data integration tool that can be used to migrate data between different systems. It can also extract, transform and load data from various sources, and perform tasks such as backing up data and archiving logs. This video training and different sections provide a practical approach to learning SSIS, with examples and step-by-step instructions for setting up the development environment, creating and manipulating packages, and developing advanced SSIS projects.
These SSIS courses cover a wide range of topics, including importing and exporting data from flat files, Excel files and XML documents, and using SSIS for data aggregation and merging. These various lectures are aimed at both beginners and experienced IT professionals who want to improve their BI skills with the Microsoft BI stack.