SSIS LOOKUP TRANSFORMATIONS

SSIS (SQL Server Integration Services) Lookup Transformations are components used in data flow tasks to perform lookups on a dataset, typically to retrieve additional information from a reference table based on a matching key. Add and configure the Lookup Currency Key transformation

1. In the SSIS Toolbox, expand Common, and then drag Lookup onto the design surface of the Data Flow tab. Place Lookup directly below the Extract Sample Currency Data source.

2. Select the Extract Sample Currency Data flat file source and drag its blue arrow onto the newly added Lookup transformation to connect the two components.

3. On the Data Flow design surface, select Lookup in the Lookup transformation, and change the name to Lookup Currency Key.

4. Double-click the Lookup Currency Key transformation to display the Lookup Transformation Editor.

5. On the General page, select Full cache.

6. In the Connection type area, select OLE DB connection manager.

1. On the Connection page, in the OLE DB connection manager dialog box, ensure that localhost.AdventureWorksDW2022 is displayed.

2. Select Use results of a SQL query, and then enter or paste the following SQL statement:

SQLCopy

SELECT * FROM [dbo].[DimCurrency]

WHERE [CurrencyAlternateKey]

IN (‘ARS’, ‘AUD’, ‘BRL’, ‘CAD’, ‘CNY’,

    ‘DEM’, ‘EUR’, ‘FRF’, ‘GBP’, ‘JPY’,

    ‘MXN’, ‘SAR’, ‘USD’, ‘VEB’)

Select Preview to verify the query results.

1. On the Columns page, in the Available Input Columns panel, drag CurrencyID to the Available Lookup Columns panel and drop it on CurrencyAlternateKey.

1. In the Available Lookup Columns list, select the check box to the left of CurrencyKey.

2. Select OK to return to the Data Flow design surface.

3. Right-click the Lookup Currency Key transformation and select Properties.

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

Add and configure the Lookup Date Key transformation

1. In the SSIS Toolbox, drag Lookup onto the Data Flow design surface. Place this Lookup directly below the Lookup Currency Key transformation.

2. Select the Lookup Currency Key transformation and drag its blue arrow onto the new Lookup transformation to connect the two components.

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

4. On the Data Flow design surface, select the name Lookup in the newly added Lookup transformation and change that name to Lookup Date Key.

5. Double-click the Lookup Date Key transformation.
6. On the General page, select Partial cache.

7. On the Connection page, in the OLEDB connection manager dialog, ensure that localhost.AdventureWorksDW2022 is displayed.

8. In the Use a table or view box, enter or select [dbo].[DimDate].

9. On the Columns page, in the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey. If you see a message indicating a data type mismatch, change the data type of CurrencyDate to [DT_DBDATE].

10. In the Available Lookup Columns list, select the check box to the left of DateKey.

11. On the Advanced page, review the caching options.

12. Select OK to return to the Data Flow design surface.

13. Right-click the Lookup Date Key transformation and select Properties.
14. In the Properties window, verify that the LocaleID property is English (United States) and the DefaultCodePage property is 1252.