Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 3 July 2014

Components of Data flow?

Components includes -
  1. Data source(s).
  2. Transformations.
  3. Destination(s).
Component 1 -  Data Flow Sources
Data Flow SourcesDescription
OLE DB SourceConnects to OLE DB data source such as SQL Server, Access, Oracle, or DB2.
Excel SourceReceives data from Excel spreadsheets.
Flat File SourceConnects to a delimited or fixed-width file.
Raw File SourceDo not use connection manager. It produces a specialized binary file format for data that is in transit.
XML SourceDo not use connection manager. Retrieves data from an XML document.
ADO.NET SourceThis source is just like the OLE DB Source but only for ADO.NET based sources.
CDC SourceReads 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 SourceReads data out of table by using an ODBC provider instead of OLE DB.

Transformation CategoriesTransformations
Row TransformationsCharacter 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
Component 3 Data Flow Destinations
Data Flow DestinationsDescription 
 ADO.NET DestinationExposes data to other external processes such as a .NET application.
 Data Reader DestinationAllows the ADO.NET Data Reader interface to consume data, similar to the ADO.NET Destination.
 OLE DB DestinationOutputs data to an OLE DB data connection like SQL Server, Oracle or Access.
 Excel DestinationOutputs data from the Data Flow to an Excel spreadsheet.
 Flat file DestinationEnables you to write data to a comma-delimited or fixed-width file.
 Raw file DestinationOutputs 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 DestinationOutputs data to an OLE DB data connection like SQL Server, Oracle or Access.
 Record set DestinationWrites 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 DestinationThe destination that you use to write 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.