Step 5: Modifying the OLE DB Destination
Earlier in lesson 2, you updated the SQL statement in the Execute SQL task, Preparation SQL Task, to include a definition of the FullName column in the Query table. In this task, you will modify the OLE DB destination, Destination - Query, to support the FullName column.
You will also restore the column mappings in Destination - Query that are no longer valid because you added a Sort transformation to the data flow. The Sort transformation generates a new set of columns with different column identifiers, and you therefore need to remap the input columns and destination columns in Destination - Query.
To modify the OLE DB destination
If not already open, open the Data Flow designer, either by double-clicking Data Flow Task or by clicking the Data Flow tab.
Click the Derived Column transformation named Add FullName Column anddrag its green arrow to Destination - Query.
Double-click Destination - Query.
In the Restore Invalid Column Reference Editor dialog box, click Select All, select the <Map using column name> option in the Column mapping option for selected rows list, and then click Apply.
You can clear the Include downstream invalid column references check box. In this package, there are no downstream data flow components and this option has no effect.
Click OK.
Right-click Destination - Query and click Show Advanced Editor.
In the Advanced Editor dialog box, click the Input and Output Properties tab, expand Destination Input, click External Columns, and then click Add Column.
A new column named Column is added to the External Columns folder.
Click the new column.
In the right-hand pane, update the Name property to FullName, click the DataType property and select Unicode string [DT_WSTR] from the list. Update the Length property to 103.
Click the Columns Mappings tab, and scroll down to the row with FullName in the Destination Column list. Click <ignore> in the Input Column list of that row, and then click FullName in the list.
Verify that all input and output columns that have the same names are mapped.
Click OK.