SQL Server Integration Services (SSIS)

The SQL Server Integration Services (shortly called as SSIS) is a powerful ETL tool, which is used for building enterprise level data transformation, and data integration solutions. SSIS is the second largest tool to perform Extraction, Transformation and Load (ETL) operations.

Microsoft included many built-in tasks and transformations in SQL Server integration Services to solve the complex business problems by building high performance data integration packages. You can use SSIS for updating data warehouses, data mining, downloading or copying files, extract and transfer data from XML to SQL etc.

TIP: You don’t have to be Pro, but try to Learn some basics on SQL Server (atleast SELECT Statements).

NOTE: Please refer Install SQL Server Data Tools article to understand the steps involved in installing the SQL Server Data Tools or Business Intelligence Development Studio (BIDS)

SSIS Connection Manager

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. FTP Connection Manager
9. SMO Connection Manager

SSIS Sources

1. Source Assistant
2. ADO.NET Source
3. Excel Source
4. OLE DB Source
5. Flat File Source

SSIS Destinations

1. ADO.NET Destination
2. OLE DB Destination
3. FLAT FILE Destination

SSIS Containers

1. For Loop Container
2. ForEach Loop Container – File Enumerator
3. ForEach Loop Container – SMO Enumerator

SSIS Transformations

1. 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 Transformation
13. Fuzzy Lookup Transformation
14. Import Column Transformation
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. Merge Join Transformation
    1. Inner Join Using Merge Join Transformation
    2. Left Outer Join Using Merge Join Transformation
    3. Right Outer Join Using Merge Join Transformation
    4. Full Outer Join Using Merge Join Transformation
18. Multicast Transformation
19. OLE DB Command Transformation
    1. Delete Operations
    2. Update Operations
20. Percentage Sampling Transformation
21. Pivot Transformation in Sql Server Integration Services 2008R2
22. Pivot Transformation in Sql Server Integration Services 2014
23. Row Count Transformation
24. Row Sampling Transformation
25. Script Component As Source
26. Use Script Component as Transformation
27. Script Component as Destination
28. 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 Transformation
33. Unpivot Transformation

SSIS Control Flow Tasks

1. Bulk Insert Task
2. Data Profiling Task
3. Execute T-SQL Statement Task
4. 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 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 SQL Server Table Structures without Data
   3. Transferring SQL Server Stored Procedures
   4. Transfer SQL Server User Defined Function
   5. Transferring SQL Server Views
10. Web Service Task
11. XML Task – Validate XML File
    1. Transform XML File Using XSLT
    2. Differentiate between XML Files

SSIS Deployment

1. Create Integration Service Catalog
2. Deploy Projects using BIDS
3. Deploy Project using SQL Server
4. Use SQL Deployment Wizard to Deploy Project
5. Breakpoints
6. Checkpoints
7. Error Handling
8. Event Handlers
9. Logging
10. Package Configurations Introduction
    1. Environment Variable
    2. Registry Entry
    3. SQL Server Configuration
    4. XML Configuration File
    5. XML Configuration File Stored in Environment Variable
11. Package Protection Levels
12. Transactions

SQL Server Import and Export Data Wizard

1. Export Data From SQL Server to Flat File Using Import and Export Data Wizard
2. Import Data from Flat File to SQL Server Using Import and Export Data Wizard
3. Export Data From SQL Server to Excel File Using Import and Export Data Wizard
4. Import Data from Excel File to SQL Server Using Import and Export Data Wizard

SSIS Interview Questions

1. Incremental Load
   1. Incremental Load
   2. Better Approach – Incremental Load
2. Export data From SQL Server to Excel File
3. Export data 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. Send Emails using Script Task

Thank You for Visiting Our blog