Step 7: Adding and Configuring the OLE DB Destination
Your package now can extract data from the flat file source and transform that data into a format that is compatible with the destination. The next task is to actually load the transformed data into the destination. To load the data, you must add an OLE DB destination to the data flow. The OLE DB destination can use a database table, view, or an SQL command to load data into a variety of OLE DB-compliant databases.
In this procedure, you add and configure an OLE DB destination to use the OLE DB connection manager that you previously created.
To add and configure the sample OLE DB destination
In the Toolbox, expand Data Flow Destinations, and drag OLE DB Destination onto the design surface of the Data Flow tab. Place the OLE DB destination directly below the Lookup DateKey transformation.
Click the Lookup Date Key transformation and drag the green arrow over to the newly added OLE DB Destination to connect the two components together.
In the Input Output Selection dialog box, in the Output list box, click Lookup Match Output, and then click OK.
On the Data Flow design surface, click OLE DB Destination in the newly added OLE DB Destination component, and change the name to Sample OLE DB Destination.
Double-click Sample OLE DB Destination.
In the OLE DB Destination Editor dialog box, ensure that localhost.AdventureWorksDW is selected in the OLE DB Connection manager box.
In the Name of the table or the view box, type or select [dbo].[FactCurrencyRate].
Click Mappings.
Verify that the AverageRate, CurrencyKey, EndOfDayRate, and TimeKey input columns are mapped correctly to the destination columns. If same-named columns are mapped, the mapping is correct.
Click OK.
Right-click the Sample OLE DB Destination destination and click Properties.
In the Properties window, verify that the LocaleID property is set to English (United States) and theDefaultCodePage property is set to 1252.