Incremental Load in SSIS 15

SSIS Tutorial | SQL Server Integration Services

SSIS Tutorial : The SQL Server Integration Services, shortly called as SSIS is a powerful ETL tool. This SSIS tutorial covers all the topics on Connection Managers, Data Sources, Transformations, Control Flow, and Data flow tasks. SSIS ETL tool Extracts data from different sources and Transform that Data as per user requirements and Load data into various destinations.

Remember, it is the second-largest tool to perform Extraction, Transformation, and Load (ETL process) operations. Although it is the most powerful tool, you can easily learn SSIS tutorial in 28days (Maximum).

SSIS ETL tool helps to build enterprise level data transformation and data integration solutions. This ETL tool is beneficial in loading data from regular transactional data into Data Warehouse. So that you can create reports using that 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 you start learning the SSIS tutorial, I suggest you learn SQL Server basics, at least SELECT Statements.

SSIS Tutorial Introduction

The Microsoft SQL Server Integration Services or SSIS included many built-in tasks and transformations to solve complex business problems by building high performance data integration packages. You can use SQL SSIS for updating warehouses, data mining, downloading or copying files, extract and transfer data from XML to SQL, etc. To develop or create an integration packages, you need SQL Data Tools or BIDS. I suggest you install the SQL Data Tools, Visual studio or Business Intelligence Development Studio (BIDS) to practice this SSIS tutorial.

  1. What is SQL Server Integration Services
  2. Install SQL Server Data Tools or BIDS
  3. Create a New Integration Services Project
  4. Create a New Package

SSIS Tutorial on Data Flow

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

While working with the SSIS tutorial on data flow components, you always have to check the columns mappings, and this is a place whether you might end up with errors. I mean wrong column mapping or no column mapping, etc.

NOTE: I suggest you download Adventure Works, and Adventure works DW databases to practice the examples of this SSIS tutorial. Because we use these two databases in this entire SSIS tutorial. Please refer to Install Adventure Work and SELECT Statements in SQL Server.

SSIS Tutorial on Connection Managers

The connection manager contains the physical connection string properties that will create or establish a connection at a run time. SQL Integration Services allows us to develop both the project level and package level connection managers.

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

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

  1. SSIS Tutorial on 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. FTP Connection Manager
  9. SMO Connection Manager

SSIS Tutorial on Sources

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

The main motive of the SQL integration package is to transfer data from various sources to a destination. It means you need a Source to get the data from and a Destination to load into it. The following are the list of common sources used in the package development.

  1. SSIS tutorial on Source Asst.
  2. ADO.NET Source
  3. Excel Source
  4. OLE DB Source
  5. Flat File Source

SSIS Tutorial on Destinations

  1. SSIS tutorial about the ADO.NET Destination
  2. OLE DB Destination
  3. FLAT FILE Destination

SSIS tutorial on Transformations

SQL Integration Service is all about Sources, Transformations, and Destinations. You need Connection Managers to connect with sources and destinations. So, if you know the connection Manager, then you do not have to think about the Sources and Destinations.

Spend 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 Source and Destination. However, you can skip those steps

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

  1. SSIS tutorial about the List of Transformations
  2. Audit Transformation
  3. Aggregate Transformation Basic Mode
  4. Aggregate Transformation 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 tutorial on 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
  20. Percentage Sampling
  21. Pivot Transformation in Integration Services 2008R2
  22. Pivot Transformation in Integration Services 2014
  23. Row Count
  24. Row Sampling
  25. Script Component As Source
  26. Use Script Component as Transformation
  27. Script Component as Destination
  28. SSIS tutorial on Slowly Changing Dimension
    1. SCD Type 0
    2. Slowly Changing Dimension Type 1
    3. SCD Type 2
  29. Sort Transformation
  30. Term Lookup Transformation
  31. Term Extraction Introduction
    1. Extract Nouns Only
    2. Extracting Noun Phrases Only
    3. Extract Nouns and Noun Phrases
    4. Exclusion Tab
  32. Union All
  33. Unpivot

By the 15th day of this SSIS tutorial, you know the Connection Managers, Sources, Destinations, and Transformations. Learn SSIS containers and the Control Flow tasks from 15th to 20th.

SSIS Tutorial on Control Flow Tasks

Every SQL Integration Services package consists of a control flow tab. Within this control flow tab, you can optionally use the control flow tasks, one or more data flow tasks. Perhaps, you can use the combination of both the control flow task and data flow tasks.

SSIS Containers Tutorial

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

  1. SSIS tutorial on For Loop Container
  2. SSIS tutorial about the ForEach Loop Container
    1. ADO.NET Schema Rowset Enum
    2. File Enum
    3. NodeList Enum
    4. SMO Enum
    5. Variable Enum

SSIS Tutorial on Control Flow Tasks

This section of the SSIS tutorial explains everything about the control flow tasks.

  1. Bulk Insert Task
  2. Data Profiling Task
  3. Execute T-SQL Statement Task
  4. SSIS tutorial on Execute SQL Task
    1. Introduction to Execute SQL Task
    2. Truncate Table using Execute SQL Task – Result Set None
    3. Execute SQL Task – Single Row Result Set
    4. Executing SQL Task – Full Result Set
  5. Execute Package Task
    1. Executing Packages in File System
    2. Execute Packages in SQL 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. 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
  10. Web Service Task
  11. XML Task – Validate XML File
    1. Transform XML File Using XSLT
    2. Difference between XML Files

SSIS Deployment Tutorial

Once we complete the building or developing the SQL integration package, the next step is deployment. Spend your last two days (21, 22) to learn SSIS catalog and deployment methods.

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

You can use either of these options to deploy packages to SQL Server. This SSIS tutorial section covers the most important topics, which are useful for the production environment.

  1. SSIS tutorial about 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. SQL Server Configuration
    5. XML Configuration File
    6. XML Configuration File Stored in Environment Variable
  12. Package Protection Levels
  13. Transactions

SQL SSIS Tutorial on Import and Export Data Wizard

If you quickly want to move or load from source to destination, you can use the Import and Export wizard. You can perform the following import and export operations using a Wizard. This wizard might act as a starting point of SSIS Tutorial or package development.

  1. Export From SQL to Flat File using Import & Export Wizard
  2. Import from Flat File to SQL using Import Export Wizard
  3. Export Data in SQL to Excel using Wizard
  4. Import from Excel to SQL using Import Export Wizard

Interview Questions

The following are the list of few SSIS interview questions that you might face apart from regular Transformation questions

  1. SSIS tutorial on Incremental Load
    1. Incremental Load
    2. Better Approach – Incremental Load
  2. Export From SQL Server to Excel File
  3. Export From SQL Server to Flat File
  4. Query builder
  5. How to remove Double Quotes in Excel Sheet
  6. How to remove Double Quotes in Flat or CSV File
  7. Text Qualifier Property
  8. Difference between Project Level and Package Level Connection managers
  9. Project Parameters Vs. Package Parameters
  10. Send Emails using Script Task

Comments are closed.