This SSIS tutorial covers all the topics on Connection Managers, Data Sources, Transformations, and Control Flow Tasks. The SQL Server Integration Services, shortly called as SSIS is a powerful ETL tool.
SSIS is an ETL tool, which is used to Extract data from different sources and Transform that Data as per user requirements and Load data into various destinations. Remember, SSIS is the second-largest tool to perform Extraction, Transformation, and Load (ETL) operations.
SSIS ETL tool helps to build enterprise-level data transformation and data integration solutions. This SSIS 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 use SSIS to work on typical data integration applications as well
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 AdventureWorks to understand the process of install and download the databases.
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 data warehouses, data mining, downloading or copying files, extract and transfer data from XML to SQL, etc.
To develop or create an SSIS package, you need SQL Data Tools or BIDS. So, I suggest you install the SQL Data Tools, or Business Intelligence Development Studio (BIDS) to practice this SSIS tutorial
- What is SQL Server Integration Services
- Install SQL Server Data Tools or BIDS
- How to Learn SQL Integration Services in 28 Days
- Create a New Integration Services Project
- Create a New Package
SSIS Data Flow
There are three types of SSIS data flow components: Sources, Transformations, and Destinations. Each data flow component has an output, and you can use the output to connect with other components. For instance, connect the source output to SSIS transformations and then connect transformation output to destination.
While working with the SSIS 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.
SSIS Connection Manager
The SSIS connection manager contains the physical connection string properties that will create or establish a connection at a run time. SSIS allows us to develop both the project level and package level connection managers. The SSIS project level connection managers shared among all the packages in that project. It is available at the SSIS project deployment.
This section of SSIS tutorial covers establishing connections between this SQL SSIS and different data sources.
- Connection Managers Introduction
- OLE DB Connection Manager
- ADO Connection Manager
- ADO.NET Connection Manager
- Cache Connection Manager
- EXCEL Connection Manager
- File Connection Manager
- FTP Connection Manager
- SMO Connection Manager
The main motive of the SSIS package is to transfer data from heterogeneous sources to a destination. It means you need a Source to get the data from and a Destination to load data into it. The following are the list of common sources used in the SSIS package development.
SSIS Transformations tutorial
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 SSIS transformation.
- List of Transformations
- Audit Transformation
- Aggregate Transformation Basic Mode
- Aggregate Transformation Advanced Mode – Configure Multiple Outputs
- Cache Transformation
- Character Map Transformation
- Conditional Split Transformation
- Copy Column Transformation
- Data Conversion Transformation
- Derived Column Transformation
- Export Column Transformation
- Fuzzy Grouping Transformation
- Fuzzy Lookup Transformation
- Import Column Transformation
- Lookup Introduction
- Merge Transformation
- Merge Join Transformation
- Multicast Transformation
- OLE DB Command Transformation
- Percentage Sampling Transformation
- Pivot Transformation in SQL Server Integration Services 2008R2
- Pivot Transformation in SQL Server Integration Services 2014
- Row Count Transformation
- Row Sampling Transformation
- Script Component As Source
- Use Script Component as Transformation
- Script Component as Destination
- SSIS Slowly Changing Dimension
- Sort Transformation
- Term Lookup Transformation
- Term Extraction Introduction
- Union All Transformation
- Unpivot Transformation
SSIS Control Flow Tasks Tutorial
Every SSIS 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 SSIS control flow tasks and data flow tasks.
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.
- For Loop Container
- ForEach Loop Container
This section of the SSIS tutorial explains everything about the control flow tasks.
- Bulk Insert Task
- Data Profiling Task
- Execute T-SQL Statement Task
- SSIS Execute SQL Task
- Execute Package Task
- File System Task
- FTP Task
- Script Task
- Transfer SQL Server Objects Task
- Web Service Task
- XML Task – Validate XML File
Once we complete the building or developing the SSIS package, the next step is deployment. Deploying the SSIS 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 SSIS 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 SSIS packages to SQL Server. This SSIS tutorial section covers the most important topics, which are useful for the production environment.
- Create Integration Service Catalog
- Deploy Projects using BIDS
- Deploy Project using SQL Server
- Use SQL Deployment Wizard to Deploy Project
- Error Handling
- Event Handlers
- Package Configurations Introduction
- Package Protection Levels
SQL SSIS Import and Export Data Wizard
If you quickly want to move or load data from source to destination, you can use the SSIS Import and Export wizard. You can perform the following import and export operations in SSIS using a Wizard. This wizard might act as a starting point of SSIS Tutorial or package development.
- Export Data From SQL to Flat File using Import & Export Data Wizard
- Import Data from Flat File to SQL using Import Export Data Wizard
- Export Data in SQL to Excel using Wizard
- Import Data from Excel to SQL using Import Export Wizard
SSIS Interview Questions
The following are the list of few SSIS interview questions that you might face apart from regular SQL SSIS Transformation questions
- SSIS Incremental Load
- Export Data From SQL Server to Excel File
- Export Data From SQL Server to Flat File
- Query builder
- How to remove Double Quotes in Excel Sheet
- How to remove Double Quotes in Flat or CSV File
- Text Qualifier Property
- Difference between Project Level and Package Level Connection managers
- Project Parameters Vs. Package Parameters
- Send Emails using Script Task