SSIS Data Flow allows tasks to execute in a particular sequence, which has been predetermined by the logic of your SSIS Package implementation. It allows you to have flexibility of SSIS Package execution from start to end. The main objective of the SSIS Data Flow is to manage transformation of the data while it is in transit. In fact, it is responsible for any activity that involves data manipulation.
SSIS Data Flow consists of several component that you can add to the Data Flow design interface.
- 1. SSIS Sources are components which you configure via Connection Manager. Each of the configured of built-in source can be reused throughout your packages. There are six, out of the box, sources
- OLE DB Source is designed to connect to OLE DB compliant data source: SQL, DB2, MS Access, Oracle or Sybase.
- Raw File Source is designed to retrieve data from binary format file.
- XML Source is designed to retrieve data from an XML based document.
- Excel Source is designed to connect and perform data import from MS Excel.
- ADO.NET Source is recent addition to SSIS and works just like OLE DB but only for ADO.NET base sources.
- Destinations are designed to consume data after this data has passed transformation. It is managed through the SSIS Connection Manager. There are 12 out of the box destinations exist in SSIS.
- Data Mining Model Training analyzes data model
- Dimension Processing can perform full or incremental dimension refresh
- Excel Destination brings data into Excel
- Data Reader Destination allows ADO.NET DataReader interface to consume data
- Flat File Destination brings data into Flat File
- OLE DB Destination sends data into OLE DB compliant database such as SQL, DB2, MS Access, Oracle or Sybase.
- ADO.NET Destination is designed to expose data to external processes such as SSRS, .NET applications and ADO.NET compliant destinations
- Partition Processing performs update of an SSAS partition
- Raw File Destination send data to Binary files
- Recrodset Destination send records to ADO RecordSet
- SQL Server Destination uses SQL Server as its final destination for the data
- SQL Server Compact Edition Destination send data to SQL Server compact edition
- Transformations designed to change data or transform it as required by the process. Transform components can sort, filter, modify data as well as accept or reject while it’s in the data pipe in-memory. There are several transforms available with SSIS out of the box install
It is designed to aggregate data
It is designed to information in the data pipe
It is designed to change casing of the data
It is designed to split data based on the condition
It is designed to make a copy of any column
It is designed to convert datatypes
Data Mining Query
It runs queries against SSAS
It creates new column from an expression
It is designed to export data from Data Flow into a file
It is designed to clean up data based on fuzzy logic
It is designed to standardize data based on fuzzy logic
It reads data from a file
It does a lookup to be used in the transformation
It is designed to merge data sets into one
It uses join to merge two data sets into one
It send copied data to a different path
OLE DB Command
It executes OLE DB command
It captures sample data from the DataFlow
It is designed to pivot data like you would do it in Excel
It gets row count from the Data Flow
It performs sampling of the data in the DataFlow
It uses scripting to perform data transformation
Slowly Changing Dimension
It is designed to coordinate conditional update in a dimension
It is designed to data in the DataFlow
It is designed to lookup noun or adjectives in data
It is designed to look up a certain word.
It merges two or more datasets
It is designed to unpivot data