This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012.  So far, we’ve covered:

• Tables and Schemas in the data warehouse
• Dimensions and Slowly Changing Dimensions
• Fact tables and measures
• Intro to columnstore indexes
• Columnstore indexes and partitioning
• Introduction to SQL Server Data Tools (SSDT)
• Connection Managers
• Control Flow Tasks and Containers
• Data Flow Source Adapters

Last time, we began exploring the data flow task by introducing the data source adapter.  The data source represents the Extract portion of the ETL process.  Today, we’re discussing the Load component, implemented in SSIS by the data flow destination adapter.

Data Flow Destination Adapters

The destination adapter is the end point of your data flow, when the data gets loaded into its final location.  Just as with source adapters, you have several destination adapters to choose from, depending on where the data is going.

• ADO.NET destination – load data into a destination using an ADO.NET provider
• Data Mining Model Training – sends data into an SSAS data mining model
• DataReader destination – send data to another program using an ADO.NET recordset
• Dimension Processing – load and process an SSAS dimension
• Excel destination – write data to an Excel spreadsheet
• Flat File destination – create a delimited text file
• ODBC destination – load data using an ODBC provider.  This destination can be configured to load data row by row or in batches, for faster processing.  One drawback of the ODBC destination is you can’t insert into identity columns.
• OLE DB destination – load data using an OLE DB provider.  This destination can be configured to use bulk inserts via the “fast load” option.  You can also specify the rows per batch, the commit size, and whether a table lock is used, identity and null values are retained, and constraints are checked while loading.
• Partition Processing – load data into an SSAS partition
• Raw File destination – insert data into a native SSIS file that can later be read through the Raw File source.
• RecordSet destination – stores data in a package object variable for use outside of the data flow
• SQL Server Compact destination – send data to a mobile device running SQL Mobile
• SQL Server destination – send data to, you guessed it, a SQL Server database.  This destination offers very high throughput, however the package needs to run on the same server as the destination instance.

Configuring the Destination Adapter

When you add a destination adapter, you’ll need to configure it.  But before you do that, you want to make sure you have a data flow path connected to the destination, so that SSIS knows the input columns the destination needs to map.  This data flow path can come directly from a source adapter or from a transformation.  You can also have several data flow paths feeding into a destination.  To connect the data flow path, grab the blue arrow coming out of your source (or transformation) and drag it to your destination adapter.  Then double-click on the destination to edit its configuration.  Today we’re configuring an OLE DB destination.

The first page you’ll see in the OLE DB destination editor is the Connection Manager.  Here you can select an existing OLE DB connection manager or you can create a new one.  You will then select an access mode.  The options are:

• Table or view – rows will be inserted into the selected table or view one row at a time
• Table or view – fast load – rows are inserted in batches using bulk insert statements
• Table name or view name variable – rows will be inserted into a table or view one row at a time.  The name of the target object is specified in a package-level variable.
• Table name or view name variable – rows will be inserted into a table or view in batches using bulk insert statements.  The name of the target object is specified in a package-level variable.
• SQL Command – a query describing the destination metadata.  For example, “SELECT * FROM dbo.Product” will load the data into dbo.Product.  I can’t really think of a scenario where this would be useful, especially when you lose the ability to perform bulk inserts or keep identity values.

OLE DB Destination – Connection Manager

Most often, you’ll probably use the Table or View – fast load option for performance and flexibility.  Once you’ve selected your destination, you can move on to the Mappings page to map the input columns from the data flow path to the columns in the destination.

OLE DB Destination – Mappings

Just like with the source adapter, you can right-click on the destination adapter and select “Show Advanced Editor…” to edit advanced settings of the destination, including its properties.  Or you can edit the properties through the Properties window of the SSDT environment.

Summary

There are quite a few options to choose from when it comes to selecting a destination adapter.  Pay close attention to the benefits and restrictions of each, as they’ll have a direct impact on usability and performance in your packages.