Using SQL Server Integration Services to Load Data - Microsoft SQL Server 2008 Analysis Services - page 405

. A data flow task contains transforms of three different types:

. Source —Reads data from a variety of sources such as relational databases,

XML, binary, and flat files

. Data Flow Transformations —Transforms data while moving it from source to

destination (for example, aggregation, merge, and join)

. Destination —Outputs data into different data stores

SSIS out of the box provides a large number of standard transforms. SSIS also provides an
extensible infrastructure for building custom SSIS adapters, such as the third-party
adapters available from Evolutionary Technologies International, Inc. (http://www.eti.
com/) and Persistent Systems Private Limited (http://www.persistentsys.com/). You can
even build your own adapters.

After building your package, you can run it directly from BI Development Studio to test it
or you can use dtexec.exe . You can also use SQL Server Agent to schedule execution of
your package.

Using Direct-Load ETL

To load and transform the data into Analysis Services, SSIS provides two data flow compo-
nents: partition processing and dimension processing. Behind the scenes, SSIS loads the
Analysis Services OLE DB provider into its process and uses it to upload data into the
Analysis server. Figure 22.2 shows a diagram of this process.

Integration Services

Data Warehouse

Source

Flat files

Analysis Services

Pipeline

…

Destination

Processing
buffer AS OLE DB

provider

XML

FIGURE 22.2 SSIS extracts data from the data warehouse, transforms it, and loads it into
Analysis Services.

NOTE
You can run the package directly using dtexec.exe/dtexecui.exe or you can use SQL
Server Agent's jobs infrastructure to schedule package execution.