Step 7: Adding and Configuring the OLE DB Destination
Applies To: SQL Server 2016
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.
In the SSIS Toolbox, expand Other Destinations, and drag OLE DB Destination onto the design surface of the Data Flow tab. Place the OLE DB destination directly below the Lookup Date Key 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.AdventureWorksDW2012 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 the New button to create a new table. Change the name of the table in the script to read NewFactCurrencyRate. Click OK.
Upon clicking OK, the dialog will close and the Name of the table or the view will automatically change to NewFactCurrencyRate.
Verify that the AverageRate, CurrencyKey, EndOfDayRate, and DateKey input columns are mapped correctly to the destination columns. If same-named columns are mapped, the mapping is correct.
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.