CONFIGURING SSIS OLE DB DESTINATION

SSIS OLE DB Destination is a component in SQL Server Integration Services (SSIS) used to load (insert) data into a relational database using an OLE DB provider.

Add and configure the sample OLE DB destination

1. 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.

2. Select the Lookup Date Key transformation and drag its blue arrow over to the new OLE DB Destination to connect the two components together.

3. In the Input Output Selection dialog, in the Output list box, select Lookup Match Output, and then select OK. 

4. On the Data Flow design surface, select the name OLE DB Destination in the new OLE DB Destination component, and change that name to Sample OLE DB Destination.
5. Double-click Sample OLE DB Destination.
6. In the OLE DB Destination Editor dialog, ensure that localhost.AdventureWorksDW2022 is selected in the OLE DB Connection manager box.

7. In the Name of the table or the view box, enter or select [dbo].[FactCurrencyRate].

8. If a table named NewFactCurrencyRate currently exists, delete it now. You will create the table in the next step.

9. Select the New button to create a new table. Change the name of the table in the script from Sample OLE DB Destination to NewFactCurrencyRate. Select OK.

10. Upon selecting OK, the dialog closes and the Name of the table or the view automatically changes to NewFactCurrencyRate.
11. Select Mappings.

12. 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.

13. Select OK.
14. Right-click the Sample OLE DB Destination and select Properties.

15. In the Properties window, verify that the LocaleID property is set to English (United States) and the DefaultCodePage property is set to 1252.