Microsoft SQL Server 2005 Integration Services

2017-07-07 02:10:07

The destination adapters provide the means to write the results of the data flow to an output. The output isn't always a database table or flat file. For example, the DataReader Destination Adapter writes to an in-memory dataset and doesn't even require a connection manager.

DataReader Destination

The DataReader Destination Adapter is useful for integrating the data flow from a package into external applications that support consuming data from datasets. This destination requires some additional setup on the client that consumes the data. This is useful for getting data into clients that typically only support a specific database server or for doing real-time transformation on data before landing it to the client. Table 20.30 provides the profile for this component.

Table 20.30. The DataReader Destination Adapter Profile
Property	Value	Description
Component Type	Destination
Has Custom Editor	No
Internal File I/O	No
Output Types	None
Threading	Single
Managed	Yes
Number Outputs	0
Number Inputs	1
Requires Connection Manager	No
Supports Error Routing	No

Setting Up the Component

Figure 20.39 shows the Advanced Editor for the DataReader Destination Adapter. The columns selected are included in the dataset.

Figure 20.39. The DataReader Destination Adapter

Recordset Destination

The Recordset Destination Adapter populates an ADO recordset and stores it in a package variable that can be used outside of the data flow. For example, you can generate a recordset that can be used later in the same package workflow in a Foreach Loop or Script Task. Table 20.31 provides the profile for this component.

Table 20.31. The Recordset Destination Adapter Profile
Property	Value	Description
Component Type	Destination
Has Custom Editor	No
Internal File I/O	No
Output Types	None
Threading	Single
Managed	No
Number Outputs	None
Number Inputs	1
Requires Connection Manager	No
Supports Error Routing	No
Constraints	Memory Resident	Populates an in-memory recordset and can consume a lot of memory for large datasets.

Setting Up the Recordset Destination Adapter

To set up the Recordset Destination Adapter, simply open the Advanced Editor and specify an object type variable where the recordset will be stored.

SQL Server Destination

The SQL Server Destination Adapter is a high-performance, shared memory insert mechanism. It uses the bulk insert API and directly inserts rows from the buffer to the SQL Server engine memory. For getting data into new SQL Server tables, this is the fastest method, hands down. Table 20.32 provides the profile for this component.

Table 20.32. The SQL Server Destination Adapter Profile
Property	Value	Description
Component Type	Destination
Has Custom Editor	Yes
Internal File I/O	No
Output Types	None
Threading	Multiple
Managed	No
Number Outputs	None
Number Inputs	1
Requires Connection Manager	Yes	Only for discovering server and database. Does not use the OLE DB connection.
Supports Error Routing	No
Constraints	Local Only	Can only be used to access servers running on the same machine as the package.

Setting Up the SQL Server Destination Adapter

To set up the SQL Server Destination Adapter, specify an OLE DB Connection Manager and the table to insert into. Then select the Mapping tab and map the columns. That's about it. Figure 20.40 shows the SQL Server Destination Editor.

Figure 20.40. The SQL Server Destination Editor

Advanced Settings

The SQL Server Destination Adapter also provides some knobs and switches you can tweak to modify the insert. The advanced options are listed in Table 20.33.

Table 20.33. The SQL Destination Insert Options
Term	Definition
Check Constraints	Select to check the table and column constraints.
Keep Nulls	Select to replace blank columns from the text file with nulls.
Enable Identity Insert	Select to retain null values during the bulk insert operation, instead of inserting any default values for empty columns.
Table Lock	Select to lock the table during the bulk insert.
Fire Triggers	Select to fire any insert, update, or delete triggers on the table.
First Row	Specify the number of the first row in the input to load during the bulk insert operation.
Last Row	Specify the number of the last row in the input to load during the bulk insert operation.
Max # Errors	Specify the maximum number of errors allowed before the bulk load operation is canceled. Each row that cannot be imported is counted as one error.
Ordered Columns	Specify the sorted columns.

Tip

If possible, for best performance, Table Lock should be enabled and the rest of the options should be turned off.

DataReader Destination

Table 20.30. The DataReader Destination Adapter Profile

Setting Up the Component

Figure 20.39. The DataReader Destination Adapter

Recordset Destination

Table 20.31. The Recordset Destination Adapter Profile

Setting Up the Recordset Destination Adapter

SQL Server Destination

Table 20.32. The SQL Server Destination Adapter Profile

Setting Up the SQL Server Destination Adapter

Figure 20.40. The SQL Server Destination Editor

Advanced Settings

Table 20.33. The SQL Destination Insert Options

Категории