Microsoft introduced SSIS with SQL Server 2005 and it became true ETL tool when compared to DTS. SSIS is a tool for data integration services. It has built in data warehousing tools as well as workflow tools. Its data warehouse is designed to hold data while ETL process is being performed. It is also capable of updating Multidimensional Cubes for analysis service.
SSIS features Import/Export Wizard based too for moving large quantity of data between data sources. This Import/Export tool is managed within Business Intelligence Development Studio (BIDS). BIDS has powerful user UI allowing SSIS developers use drag and drop functionality for constructing ETL process.
Other notable features are:
- Connections are used to hold information required to connect to a particular data source
- Tasks are used to performed some atomic work
- Event Handlers are used to handle events that are triggered as a result of workflow execution
- Variables are used as place holder for information that tasks may utilize during execution
- Precedence constraints are used to constraint preceding a particular task before actual execution happen.
SSIS has several important features of the dataflow tasks including the following.
SSIS provides the following built-in transformations:
- Conditional Split
- Union-All, Merge, and Merge Join
- Fuzzy Grouping
- Lookup and Fuzzy Lookup
- Percentage Sampling and Row Sampling
- Copy/Map, Data Conversion, and Derived Column
- Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension Processing
- Pivot and Unpivot
- Slowly Changing Dimension
- Script Component
- Cache Transform
- Export and Import Column
- OLE DB Command
- Row Count
- Term Extraction
- Term Lookup
Other tools help with more settled tasks such as DTEXEC and DTUTIL. Both of these tools are command based and used for helping with ETL process.