A destination is an endpoint for a data flow. It has input columns, which are determined by the connection from the previous component in the data flow pipeline, but no output.
A destination definition includes:
A connection manager for the data store where the data is to be inserted.
The table or view into which the data must be inserted (where supported).
The following table describes the kinds of destination that SSIS supports:
Databases
ADO.NET Any database for which an ADO.NET data provider is installed. OLE DB Any database for which an OLE DB provider is installed. SQL Server An SQL Server database.
SQL Server Compact An instance of SQL Server Compact.
Files
Excel A Microsoft Excel® workbook.
Flat file A text file.
Raw file An SSIS-specific binary format file. SQL Server Analysis Services
Data mining model
training Used to build data mining models for data analysis.
Dimension processing Used to populate a dimension in an online analytical processing (OLAP) cube.
Partition processing Used to populate a partition in an OLAP cube.
Rowsets
DataReader An ADO.NET DataReader interface that can be read by another application.
MCT USE ONL
Y. STUDENT USE PROHIBITED
4-18 Creating an ETL Solution with SSIS
Other sources
Script component A custom destination that is implemented as a script.
Custom A custom destination that is implemented as a .NET assembly.
To add an SQL Server, Excel, or Oracle destination to a data flow, drag the Destination Assistant icon from the Favorites section of the SSIS Toolbox pane to the design surface, and then use the wizard to select or create a connection manager. For other kinds of destination, drag the appropriate icon from the Other Destinations section of the SSIS Toolbox pane to the design surface.
After you have added a destination to the data flow, connect the output from the previous component in the data flow to the destination, double-click it, and then edit it to define:
The connection manager and destination table (if relevant) to be used when loading the data. The column mappings between the input columns and the columns in the destination.
Data Transformations
Data transformations enable you to perform operations on rows of data as they pass through the data flow pipeline. Transformations have both inputs and outputs.
The following table lists the transformations that SSIS includes:
Row transformations – update column values or create new columns for each row in the data flow
Character Map Applies string functions to column values, such as conversion from lowercase to uppercase.
Copy Column Creates a copy of a column and adds it to the data flow. Data
Conversion Converts data of one type to another, for example, numerical values to strings. Derived
Column Adds a new column based on an expression. For example, you could use an expression to multiply a Quantity column by a UnitPrice column to create a new
TotalPrice column.
Export
Column Saves the contents of a column as a file. Import
MCT USE ONL
Y. STUDENT USE PROHIBITED
Implementing a Data Warehouse with Microsoft® SQL Server® 4-19
OLE DB
Command Runs an SQL command for each row in the data flow.
Rowset transformations – create new rowsets
Aggregate Creates a new rowset by applying aggregate functions such as SUM. Sort Creates a new sorted rowset.
Percentage
Sampling Creates a rowset by randomly selecting a specified percentage of rows. Row
Sampling Creates a rowset by randomly selecting a specified number of rows.
Pivot Creates a rowset by condensing multiple records with a single column into a single record with multiple columns.
Unpivot Creates a rowset by expanding a single record with multiple columns into multiple records with a single column.
Split and Join transformations – merge or branch data flows
Conditional
Split Splits a single-input rowset into multiple-output rowsets based on conditional logic. Multicast Distributes all input rows to multiple outputs.
Union All Adds multiple inputs into a single output. Merge Merges two sorted inputs into a single output.
Merge Join Joins two sorted inputs to create a single output based on a FULL, LEFT, or INNER join operation.
Lookup Looks up columns in a data source by matching key values in the input. It creates an output for matched rows and a second output for rows with no matching value in the lookup data source.
Cache Caches data from a data source to be used by a Lookup transformation.
CDC Splitter Splits inserts, updates, and deletes from a CDC source into separate data flows. CDC is discussed in Module 7: Implementing an Incremental ETL Process.
Auditing transformations – add audit information or count rows
Audit Provides execution environment information that can be added to the data flow. RowCount Counts the rows in the data flow and writes the result to a variable.
BI transformations – perform BI tasks
Slowly Changing Dimension
Redirects rows when loading a data warehouse to preserve historical dimension values.
MCT USE ONL
Y. STUDENT USE PROHIBITED
4-20 Creating an ETL Solution with SSIS
Grouping
Fuzzy Lookup Looks up columns in a data source by finding approximate matches for values in the input.
Term
Extraction Extracts nouns or noun phrases from text for statistical analysis. Term Lookup Matches terms extracted from text with terms in a reference table. Data Mining
Query Runs a data mining prediction query against the input to predict unknown column values. Data
Cleansing Applies a Data Quality Services knowledge base to data as it flows through the pipeline.
Custom transformations – perform custom operations
Script
Component Runs custom script code for each row in the input. Custom
Component A custom .NET assembly.
To add a transformation to a workflow, drag it from the Common or Other Transforms section of the SSIS Toolbox pane to the design surface, and then connect the required inputs to the transformation. Double-click the transformation to configure the specific operation that it will perform, and then define the columns to be included in the outputs from the transformation.
Additional Reading: For more formation about Integration Services Transformations, go to http://go.microsoft.com/fwlink/?LinkID=246724.