Creating Data Flow in a Package


The data flow in an SSIS package is constructed by using different types of data flow elements: data flow sources that extract data, transformations that modify and aggregate data, destinations that load data, and paths that connect the outputs and inputs of data flow components into a data flow. To build the data flow in a package, you drag objects from the Data Flow Items group in the Toolbox onto the Control Flow designer in SQL Server Business Intelligence Development Studio (BIDS). This section discusses these data flow objects in more detail.

Data Flow Sources

Data flow sources are the data flow objects that make data from different types of data sources available to the data flow. Sources have one regular output, and many have one error output.

A data flow source reads the metadata from a connection manager and sets up a data flow pipeline in the computer's memory. It then reads the data from the source and feeds it through the pipeline's starting point.

Data Flow Transformations

SSIS transformations are the components in the data flow of a package that aggregate, merge, distribute, and modify data. Transformations also perform lookup operations and generate sample datasets.

A data flow transformation modifies data as it flows through the pipeline. As a data record flows through the pipeline, transformations might:

  • Modify the fields in the record.

  • Add new fields to the record.

  • Aggregate records.

  • Create new pipelines and send each record down every pipeline.

  • Create new pipelines and, based on the data in the record, determine to which pipeline to send the record.

  • Read data from multiple pipelines and send them into a single pipeline.

  • Perform many other types of transformations.

Data Flow Destinations

Destinations are the data flow components that flow the data into different types of data sources or create an in-memory dataset. Destinations have one input and one error output.

A data flow destination maps the pipeline metadata to the data destination metadata stored in a connection manager. The data flow destination then reads the transformed data from the end of the pipeline and writes it to the data destination.

Data Source Connections

Connection managers contain metadata about data flow sources and data flow destinations. An Open Database Connectivity (ODBC) connection manager enables a package to connect to a variety of database management systems by using the ODBC specification.

When you add an ODBC connection to a package and set the connection manager properties, SSIS creates a connection manager and adds the connection manager to the Connections collection of the package. At run time, the connection manager is resolved as a physical ODBC connection.

You create the data flow in a package by using the Data Flow designer, the design surface on the Data Flow tab in BIDS. Creating a data flow includes the following steps:

  • Adding one or more sources to extract data from files and databases.

  • Adding the transformations that meet the business requirements of the package.

  • Connecting data flow components by connecting the output of sources and transformations to the input of transformations and destinations.

  • Adding one or more destinations to load data into data stores such as files and databases.

  • Configuring error outputs on components to handle problems such as errors or data values that are truncated.

  • Adding connection managers to the package if the data flow includes components that connect to data sources. You can add connection managers while working in the Data Flow designer, but you can also add them when the Control Flow or Event Handlers tab is active.

Note 

The key information to remember is that data flows from top to bottom, and it is occasionally split (or sometimes merged) but never looped. Data flow always starts with a data source and ends with a destination data store.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net