SSIS Tutorial

SSIS Tutorial

The SQL Server Integration Services, SSIS, is a powerful ETL tool. Although it is the most powerful tool, you can quickly learn SSIS tutorials in 28 days (Maximum). Remember, it is the second-largest tool for performing Extraction, Transformation, and Load (ETL process) operations.

SSIS Tutorial Introduction

The Microsoft SQL Server Integration Services or SSIS included many built-in tasks and transformations. Use them to solve complex business problems by building high-performance data integration packages. One can use this SSIS tutorial to update warehouses, mine data, and downloading or copy files. Next, extract and transfer data from XML to SQL, etc.

You need Data Tools or BIDS to develop or create an SSIS integration package. Therefore, I suggest you install the SQL Server Data Tools, Visual Studio, or Business Intelligence Development Studio (BIDS) to practice this SSIS tutorial.

  1. Install Data Tools or BIDS
  2. Create a New Integration Services Project
  3. Create a New Package

This SSIS tutorial covers all the topics on Connection Managers, Data Sources, Transformations, Control Flow, and Data flow tasks.

What is SSIS?

Firstly, the SSIS ETL tool is a business intelligence tool that provides data transformation solutions for various organizations. It accepts and extracts data from various data sources (text, CSV, Excel, XML, database, etc.) and transforms data according to user requirements. And then loads data into various destinations such as data warehouse, text, excel CSV, etc.

Secondly, this SSIS ETL tool helps build enterprise-level data transformation and data integration solutions. It is beneficial in loading data from regular transactional data into Data Warehouse. So you can create reports using the transformed data with the help of SSRS, Tableau, etc.

Apart from the Data Warehouse application, you can also use SQL Server Integration Services to work on typical data integration applications. Before learning the SSIS tutorial, I suggest learning SQL Server basics, at least SELECT Statements.

What are the tools used in SSIS?

  • A graphical user interface to develop packages without writing any code.
  • The SSIS has many transformations and control flow tasks to clean or transform data.
  • It has a Catalog database that we configure at the beginning to deploy packages, run, and manage them from SSMS. The Integration Services rely on SQL Server Agent for scheduling the operations.

SSIS Project Requirements

You need a development environment and a Management Studio to work with the SSIS packages tutorial. And the Microsoft SQL Server provides two options to develop the package, first using BIDS or SSDT. But the second option is Import and Export wizard.

You can use the second option to perform the basic data loading because SSIS does not need a different environment. As a result, it uses the wizard to select all the sources, transformations, and destinations.

Remember, you also need one more item called SQL Management studio. It helps to monitor and manage the developed SSIS packages in a production environment.

What is SSIS Project Development Model?

The SSIS project combines Connections Managers, Packages, and project parameters (optional). Furthermore, the SQL Server Integration Services is an ETL (Extract, Transform, and Load) tool. You can follow this SSIS tutorial to extract data from various sources such as Excel Files, Flat Files, XML Files, and Relational databases. Furthermore, transform (slice and dice) them according to your requirements and load the data into the destination.

SSIS Package Work environment

The tutorial screenshot below shows the SSIS work environment and how it is used to create SQL Server Integration Services projects and learn.

SSIS Tutorial

From the above screenshot, we divided the SSIS window into different parts.

  1. Solution Explorer is a combination of project level connection managers, actual packages, and project parameters.
  2. Properties: Use this window to change the properties of every task.
  3. Toolbox: SSIS provides a lot of built-in tasks, containers, transformations, sources, destinations, and administrative tasks to solve complex business problems. Use these graphical tools by dragging and dropping those tasks in the work environment. It means we do not have to write a single line of code to perform most operations.
  4. Information: This shows the information about SSIS toolbox items, and this tutorial covered them.
  5. Connection Managers: Use this window to create package-level connection managers
  6. Package Designer window.

Important SSIS Components

Although there are many built-in components in the SSIS Integration Services, there are some situations where we need a custom task to perform some business-centric operations. The two most powerful components fulfill this: Script Task and Script Component. 

  • If you want the custom control flow task, use SSIS Script Task along with C# or VB mentioned in this tutorial and write your functions.
  • Use the script component as the Source, Destination, or Transformation for the custom data flow task.

You can also create, run, and deploy a package without opening the SSIS interface. For instance, with the .Net application, you can create a package. Next, perform some operations, and deploy them to the SQL Server catalog.

This section of the SSIS article explains everything about the control flow tasks. It includes file system tasks, FTP operations, execute package, execute process, send mail task, etc.

SSIS Tutorial on Data Flow task

There are three types of SSIS SQL Server Integration Services data flow components: Sources, Transformations, and Destinations. Each data flow component has an output. Use the result to connect with other members. For instance, connect the source output to transformations and then connect the transformation output to the destination.

While working with the SSIS tutorial on data flow components, you must check the column mappings. And this is a place where you might end up with errors. I mean wrong column mapping or no column mapping, etc.

NOTE: I suggest downloading Adventure Works and Adventure works DW databases to practice these SSIS tutorial examples. Because we use these two databases in this article, please refer to Install Adventure Work and SELECT Statements in Server.

SSIS Tutorial on Connection Managers

This SQL SSIS tutorial extracts data from different sources, transforms and loads it to a completely different destination. Firstly, we need a connection manager to establish the connection between the package and source and package and destination.

In addition, the SSIS connection manager contains the physical connection string properties to create or establish a connection at a run time. In SQL Server Integration Services, there are two types of connection managers. And they are Package Level (designed in a 5th window) and Project Level (created in the Solution Explorer window).

The SSIS project level connection managers shared among all the packages in that project. It is available at the project deployment. This tutorial section covers establishing connections between this SQL SSIS and different data sources.

Learn everything about the SSIS Connection Managers tutorial in the First two days. Generally, it takes one day to cover this topic. Most of the steps in those links are the same. I think the maximum of 2 to 3 steps may differ.

  1. Connection Managers Introduction
  2. OLE DB Connection Manager
  3. ADO Connection Manager
  4. ADO.NET Connection Manager
  5. Cache Connection Manager
  6. EXCEL Connection Manager
  7. File Connection Manager
  8. Flat File Connection Manager
  9. FTP Connection Manager
  10. SMO Connection Manager

SSIS Tutorial on Sources

Continue learning the SSIS tutorial on Sources & Destinations for the next two days. As I said, if you know the connection managers, you are good with Destinations and Sources.

The following tutorial lists common sources used in the SSIS package development.

  1. Source Assistance
  2. ADO.NET Source
  3. Excel Source
  4. OLE DB Source
  5. Flat File Source
  6. Raw File Source

SSIS Destinations

  1. ADO.NET Destination
  2. OLE DB Destination
  3. FLAT FILE Destination
  4. Raw File Destination

Basic Import & Exports of Data

The main motive of the SQL Server Integration Services package is to transfer data from various sources to a destination. It means you need an SSIS Source to get the data from and a Destination to load into.

  1. Export SQL Table Data to Colon Delimiter Text File
  2. Export SQL Data to Fixed Width Flat File
  3. Export Data to Fixed Width with Row Delimiters File
  4. Export Table Data to Pipe Delimiter Text File
  5. Export SQL Data to Ragged Right Fixed Width File
  6. Export Table Data to Semicolon Delimiter Text File
  7. Export Table Data to Tab Delimiter Text File
  8. Export Table Data to Flat File with Text Qualifier
  9. Load Data From Pipe Delimiter File to SQL Server
  10. Load Data From Tab Delimiter File to SQL Server
  11. Load Flat File Blank spaces as SQL Server Nulls
  12. Load Fixed Width Flat File Data to SQL Server
  13. Load Fixed Width With Row Delimiter File to SQL
  14. Load Ragged Right Fixed Width File to SQL
  15. Load Multiple Flat Files to SQL

SSIS tutorial on Transformations

The SQL Integration Service is all about Sources, Transformations, and Destinations. You need SSIS Connection Managers to connect with sources (SQL server database, CSV, etc.) and destinations. So, if you know the connection Manager, you do not have to think about the Sources and Destinations.

Spending the next ten days is all about transformations. I don’t think; you need 10 days to complete the transformations. Because in all the SSIS tutorial examples, we explained each transformation along with SQL Source and Destination. However, you can skip those steps.

The SSIS Transformations modify or clean the SQL Server databases data, performs data conversions, perform data aggregations, etc. The following section shows the list of available transformations. Please click the link to see the definition and practical example of each SQL Server Integration Services transformation.

SSIS Transformations List

  1. List of Transformations
  2. Audit Transformation
  3. Aggregate Transformation Basic Mode
  4. Aggregate Advanced Mode – Configure Multiple Outputs
  5. Cache Transformation
  6. Character Map Transformation
  7. Conditional Split Transformation
  8. Copy Column Transformation
  9. Data Conversion Transformation
  10. Derived Column Transformation
  11. Export Column Transformation
  12. Fuzzy Grouping
  13. Fuzzy Lookup
  14. Import Column
  15. Lookup Introduction
    1. Use OLE DB Connection Manager for Lookup Transformation
    2. Lookup Transformation in Full Cache Mode
    3. Lookup Transformation Case Sensitivity
  16. Merge Transformation
  17. SSIS Merge Join Transformation
    1. Inner Join Using Merge Join
    2. Left Outer Join Using Merge Join
    3. Right Outer Join by using Merge Join
    4. Full Outer Join
  18. Multicast Transformation
  19. OLE DB Command Transformation
    1. Delete Operations
    2. Update Operations
    3. Stored Procedures with Parameters
  20. Percentage Sampling
  21. Pivot Transformation in 2008R2
  22. Pivot Transformation in 2014
  23. Row Count
  24. Row Count Transformation to Log Inserted Rows
  25. Row Sampling
  26. Script Component As Source
  27. Use Script Component as Transformation
  28. Script Component as Destination
  29. SSIS Slowly Changing Dimension
    1. SCD Type 0
    2. Slowly Changing Dimension Type 1
    3. SCD Type 2
  30. Sort Transformation
  31. Term Lookup Transformation
  32. Term Extraction Introduction
    1. Extract Nouns Only
    2. Extracting Noun Phrases Only
    3. Extract Nouns and Noun Phrases
    4. Exclusion Tab
  33. Union All
  34. Unpivot

By the 15th day of this course, you might learn the SSIS Connection Managers, Sources, Destinations, and Transformations. Similarly, learn containers and the Control Flow tasks from the 15th to the 20th.

SSIS Tutorial on Control Flow Tasks

Every SSIS Integration Services package consists of a control flow tab. You can optionally use the control flow tasks within this control flow tab, one or more data flow tasks. Perhaps, you can use the combination of both the control flow task and data flow tasks. It is where we design the complete data flow. Furthermore, it is the place where we perform all the transformations.

Most of the SSIS control flow tasks and data flow tasks do not require any coding. As it supports the Microsoft .NET framework, developers can use their choice of supporting programming languages to build tasks. While working with script tasks, you can choose between VB.NET and C#.

SSIS Containers Tutorial

This SSIS section covers the containers available in this ETL tool. The following containers provide the structure of the package or provide looping functionality.

  1. For Loop Container
  2. ForEach Loop Container
    1. ADO Enumerator
    2. ADO.NET Schema Rowset Enum
    3. File Enumerator
    4. Item Enumerator
    5. NodeList Enumerator
    6. SMO Enumerator
    7. Variable Enumerator

SSIS Control Flow Tasks tutorial

  1. Bulk Insert Task
  2. Data Profiling Task
  3. Execute T-SQL Statement Task
  4. Execute SQL Task
    1. Introduction to Execute Task
    2. Truncate Table – Result Set None
    3. Execute Task – Single Row Result Set
    4. Executing Task – Full Result Set
    5. Using File Connection in Execute SQL Task
    6. Execute SQL Task to run Multiple Query Files
  5. Execute Package Task
    1. Executing Packages in File System
    2. Execute Packages in the Server
    3. Executing Packages present in the Same Project
  6. File System Task
    1. Copy Directory
    2. Copy File
    3. Delete Directory Content
    4. Move Directory
    5. Delete Directory
    6. Move File
    7. Move Multiple Files
    8. Delete File
    9. Rename File
    10. Set Attributes
  7. FTP Task
    1. Send Files
    2. Send Multiple Files
    3. Receive Files
    4. Receive Multiple Files
    5. Delete Local Files
    6. Create Remote Directory
    7. Delete Remote Directory
    8. Create a Local Directory
    9. Delete Local Directory
    10. Delete Remote Files
  8. Script Task
  9. Script Task to Archive Files after Loading them
  10. Transfer SQL Server Objects Task
    1. Transferring SQL Server Tables with Data
    2. Transfer Table Structures without Data
    3. Transferring Stored Procedures
    4. Transfer User Defined Function
    5. Transferring Views
  11. Web Service Task
  12. XML Task – Validate XML File
    1. Transform XML File Using XSLT
    2. Difference between XML Files

SSIS Deployment Tutorial

After building or developing the SQL integration package, the next step is to deploy the package into the production environment. Spend your last two days learning the SSIS catalog and deployment methods tutorial.

Deploying the SQL Integration Services project is where we should decide the SSIS package execution time and the authorized user to perform the same. There are different ways that you can deploy your packages. It starts using BIDS/SQL data tools, Import and Export Data wizard, and Management Studio.

Therefore, you can use either of these options to deploy packages to Server. This SSIS catalog section covers the essential topics beneficial for the production environment.

  1. Creating Integration Service Catalog
  2. Deploy Projects using BIDS
  3. Deploy Project using SQLServer
  4. Use Deployment Wizard to Deploy Project
  5. Breakpoints
  6. Checkpoints
  7. Error Handling
  8. Event Handlers
  9. Logging
  10. Parameters
  11. Package Configurations Introduction
    1. Environment Variable
    2. Parent-Child Package Configuration
    3. Registry Entry
    4. Server Configuration
    5. XML Configuration File
    6. XML Configuration File in Environment Variable
  12. Package Protection Levels
  13. System Variables
  14. Transactions

SQL SSIS Tutorial on Import and Export Data Wizard

You can use the SSIS Import and Export wizard if you quickly want to move or load from source to destination. You can perform the following import and export operations using a Wizard. This wizard might be a starting point for package development.

  1. Export from Database to Flat File using Import & Export Wizard
  2. Import from Flat File to Database using Import Export Wizard
  3. Export Data from DB to Excel using Wizard
  4. Import from Excel to Database using Import Export Wizard

SSIS Interview Questions

The following is the list of a few SSIS tutorial interview questions you might face apart from regular Transformation questions.

  1. Copy Table Data from one SQL Instance to Another
  2. Copy Tables from one SQL Instance to Another Using Data Flow Task
  3. Incremental Load
    1. Incremental Load
    2. Better Approach – Incremental Load
  4. Export From Database to Excel File
  5. Export From DB to Flat File
  6. Query Builder
  7. How to remove Double Quotes in Excel Sheet
  8. How to remove Double Quotes in Flat or CSV File
  9. Text Qualifier Property
  10. Difference between Project Level and Package Level Connection managers
  11. Project Parameters Vs. Package Parameters
  12. Replace Table Nulls using Derived Column
  13. Replace Table Nulls using Script Transformation
  14. Send Emails using Script Task

SSIS Advantages

  • SSIS is one of the best tools and is very helpful for loading data into the data warehouse. Alternatively, there are a lot of ETL tools available in the current market, and Informatica is one of the tough competitors.
  • You can also deploy packages to the cloud platform Azura. And also, you can schedule the deployment of the package to Azura.
  • We have to deploy the whole SSIS project to add one additional package, which is not the case with the Integration services. Instead, it allows you to add one or more packages to the existing project, called incremental package deployment.
  • You can restrict the SSIS Integration Services access to specific users. And. Therefore, the administrator has to run the DCOM configuration tool to grant permission.
  • The catalog database can add the Always On Availability groups option. So, whenever a database failover happens, one of the secondary nodes becomes the primary node automatically.
  • It supports the Always Encrypted feature. Therefore, it allows you to load data to encrypted database columns.
  • You can see what columns are throwing errors, find erroneous data, and effectively diagnose the issue by redirecting the wrong data to the error output.

Disadvantages of SSIS

  • To configure the SSIS event logging, you must edit the registry keys we cover in this tutorial.
  • Upgrading the SQL Server instance will not automatically upgrade the SSIS package to the current release. You have to do it on your own manually. When it comes to script components, you have to manually update the assembly versions referenced by the members.

What is SSIS vs SQL?

The Microsoft SQL Server is a relational database engine. In contrast, the Integration Services is an integral part of the SQL and an ETL tool. You can only execute the package with the SQL Server license, which means you can’t run SSIS without installing the SQL Server.

Is SSIS easy and worth learning in 2023?

Yes. It is still one of the leading ETL tools in the market. The wide variety of data flows and control flow tasks are straightforward to implement and powerful to transfer data. The best part is it comes free with the SQL Server license and is an easy and powerful tool for any company.

Although SSIS performs powerful transformations and heavy lifting, the tasks are easy to understand. The best part is you don’t need any programming knowledge. All you have to do is understand each task or transformation functionality.

Comments are closed.