The introduction of Microsoft SSIS:
SQL Server Integration Services is a component of the Microsoft SQL Server database. Software it can be used to perform a data migration tasks SSIS is a platform for data integration and workflow
applications. It features fast and Flexible data warehousing tool used for data transformation,
loading, and extraction (ETL). The tool may also use to automate maintenance of SQL Server databases
and updates to Multidimensional cube data.
Features:
1. Microsoft SQL Server Integration services (SSI) (2005, 2008, and 2012).
2. It completely managed '.net ', functions written in 'C'.SSIS Tasks 2016 Now Available
We have upgraded SSIS Tasks components to the IP Works. 2016 codebase to bring The latest in
communications technology.
1. HTTP Tasks are added to give more communication options.
2. OpenPGP is now self-contained, no longer requires GnuPG.
Overview of SSIS:
SQL server integration services (SSIS) is one of the tool. It uses to perform ETL (Extract,
Transform And Load data). While ETL processing is common in data warehousing (DW)
applications.
At a high level, SSIS provides the ability to:
1. Go back data from about any source.
2. It performs different transformations on data. E.g. it converts one type to another type. And convert to
Uppercase to lowercase, perform calculations, ECT...
3. Load data into about any source.
4. Define workflow.
The first version of SSIS is release in 2005 with SQL server. Data transmission service replaced by SSIS
It was available with SQL server 7.0, SQL server 2000. This introduced with DTS.
In this tutorial, we will step through many topics that you need to understand.
Build an SSIS package.
Our high-level outline is as follows:
1. SSIS packages are created with SQL Server Management Studio (SSMS)
2. Business Intelligence Development Studio (BIDS)
3. Creating a simple SSIS package in BIDS
4. Deploying SSIS packages
5. Executing SSIS packages
Advantages:
1. SSIS handle data from heterogeneous data sources at the same package.
2. SSIS consumes data which are difficult like FTP, HTTP, MSMQ, and Analysis services etc.…
3. SSIS provides transformation functionality.
4. Easier to maintain and package configuration
5. integrated with Microsoft Visual Studio and SQL Server.
6. Use the SQL Server Destination instead of OLE DB; which allows you to load data into SQL faster.
7. Remove network as a bottleneck for insertion of data by SSIS into SQL.
8. Better for complex transformations, multi-step operations, aggregating data from different data
Sources or types, and structured exception handling
Disadvantages:
1. To see package execution report need Management Studio.
2. If many packages are available that need to run parallel then you have a trouble.
SSIS memory usage is high and it conflicts with SQL.
3. in a case of CPU allocation it also a problematic case when you have more packages to run parallel.