SSIS: DataFlow Components

Data flow consists of Source, Transformations and Destination. The core strength of SSIS is its capability to Extract data into the server’s memory (Extraction), Transform (modified) it using different Transformations and load it to an Destination (Loading). The data flow task in SSIS sends the data in series of buffer.

Arrows connecting the data flow components to create a pipeline are known as Service paths where as arrows connecting components in control flow are known as Precedence constraints. At design time, Data viewers can also be attached to the Service paths to visualize the data.

Source	Description
OLE DB Source	Connects to OLE DB data source such as SQL Server, Oracle.
Excel Source	Receives data from Excel spreadsheets.
Flat File Source	Connects to a delimited or fixed-width file.
Raw File Source	Do not use connection manager. It produces a specialized binary file format for data that is in transit.
XML Source	Do not use connection manager. Retrieves data from an XML document.
ADO.NET Source	This source is just like the OLE DB Source but only for ADO.NET based sources.
CDC Source	Reads data out of a table that has change data capture (CDC) enabled. Used to retrieve only rows that have changed over duration of time.
ODBC Source	Reads data out of table by using an ODBC provider instead of OLE DB.

Trasnformations:

Transformations Category	Transformations
Row Transformations	Character Map, Copy Column Data Column, Derived Column OLE DB Command
RowSet Transformations	Aggregate,  Sort  Pivot/Unpivot  Percentage sampling / Row sampling
Split and Join Transformations	Conditional split, Look up   Merge,   Merge join   Multicast,   union All
Business intelligence transformations	Data Mining Query,    Fuzzy Look Up   Fuzzy Grouping,   Term Extraction   Term Look up
Script Transformations	Script
Other Transformations	Audit,  Cache Transform   Export Column,  Import Column   Row Count,  Slowly Changing Dimension (SCD)

Destinations:

Data Flow Destination	Description
ADO.NET Destination	Exposes data to other external processes such as a .NET application.
Data Reader Destination	Allows the ADO.NET Data Reader interface to consume data, similar to the ADO.NET Destination.
OLE DB Destination	Outputs data to an OLE DB data connection like SQL Server, Oracle.
Excel Destination	Outputs data from the Data Flow to an Excel spreadsheet.
Flat file Destination	Writes data to a comma-delimited or fixed-width file.
Raw file Destination	Outputs data in a binary format that can be used later as a Raw File Source. It’s usually used as an intermediate persistence mechanism.
ODBC Destination	Outputs data to an OLE DB data connection like SQL Server, Oracle.
Record set Destination	Writes the records to an ADO record set. Once written, to an object variable, it can be looped over a variety of ways in SSIS like a Script Task or a Foreach Loop Container.
SQL Server Destination	Writes data to SQL Server. This destination has many limitations, such as the ability to only write to the SQL Server where the SSIS package is executing. For example – If you’re running a package to copy data from Server 1 to Server 2, then the package must run on Server 2. This destination is largely for backwards compatibility and should not be used.