Step 6: Adding and Configuring the Lookup Transformations

  • Article

After you have configured the Flat File source to extract data from the source file, the next task is to define the Lookup transformations needed to obtain the values for the CurrencyKey and TimeKey. A Lookup transformation performs a lookup by joining data in the specified input column to a column in a reference dataset. The reference dataset can be an existing table or view, a new table, or the result of an SQL statement. In this tutorial, the Lookup transformation uses an OLE DB connection manager to connect to the database that contains the data that is the source of the reference dataset.

Note

You can also configure the Lookup transformation to connect to a cache that contains the reference dataset. For more information, see Lookup Transformation.

For this tutorial, you will add and configure the following two Lookup transformation components to the package:

  • One transformation to perform a lookup of values from the CurrencyKey column of the DimCurrency dimension table based on matching CurrencyID column values from the flat file.

  • One transformation to perform a lookup of values from the TimeKey column of the DimTime dimension table based on matching CurrencyDate column values from the flat file.

In both cases, the Lookup transformations will utilize the OLE DB connection manager that you previously created.

To add and configure the Lookup Currency Key transformation

  1. In the Toolbox, expand Data Flow Transformations, and then drag Lookup onto the design surfaceof the Data Flow tab. Place Lookup directly below the Extract Sample Currency Data source.

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

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

  4. Double-click the Lookup CurrencyKey transformation.

  5. On the General page, make the following selections:

    1. Select Full cache.

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

  6. On the Connection page, make the following selections:

    1. In the OLE DB connection manager dialog box, ensure that localhost.AdventureWorksDW is displayed.

    2. Select Use results of an SQL query, and then type or copy the following SQL statement:

      select * from (select * from [dbo].[DimCurrency]) as refTable
where [refTable].[CurrencyAlternateKey] = 'ARS'
OR
[refTable].[CurrencyAlternateKey] = 'AUD'
OR
[refTable].[CurrencyAlternateKey] = 'BRL'
OR
[refTable].[CurrencyAlternateKey] = 'CAD'
OR
[refTable].[CurrencyAlternateKey] = 'CNY'
OR
[refTable].[CurrencyAlternateKey] = 'DEM'
OR
[refTable].[CurrencyAlternateKey] = 'EUR'
OR
[refTable].[CurrencyAlternateKey] = 'FRF'
OR
[refTable].[CurrencyAlternateKey] = 'GBP'
OR
[refTable].[CurrencyAlternateKey] = 'JPY'
OR
[refTable].[CurrencyAlternateKey] = 'MXN'
OR
[refTable].[CurrencyAlternateKey] = 'SAR'
OR
[refTable].[CurrencyAlternateKey] = 'USD'
OR
[refTable].[CurrencyAlternateKey] = 'VEB'

  7. On the Columns page, make the following selections:

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

    2. In the Available Lookup Columns list, select the check box to the right of CurrencyKey.

  8. Click OK to return to the Data Flow design surface.

  9. Right-click the Lookup Currency Key transformation, click Properties.

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

To add and configure the Lookup DateKey transformation

  1. In the Toolbox, drag Lookup onto the Data Flow design surface. Place Lookup directly below the Lookup CurrencyKey transformation.

  2. Click the Lookup Currency Key transformation and drag the green arrow onto the newly added Lookup transformation to connect the two components.

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

  4. On the Data Flow design surface, click Lookup in the newly added Lookup transformation, and change the name to Lookup DateKey.

  5. Double-click the Lookup DateKey transformation.

  6. On the General page, select Partial cache.

  7. On the Connection page, make the following selections:

    1. In the OLE DB connection manager dialog box, ensure that localhost.AdventureWorksDW is displayed.

    2. In the Use a table or view box, type or select [dbo].[DimTime].

  8. On the Columns page, make the following selections:

    1. In the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey.

    2. In the Available Lookup Columns list, select the check box to the right of TimeKey.

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

  10. Click OK to return to the Data Flow design surface.

  11. Right-click the Lookup Date Key transformation and click Properties.

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

Next Task in Lesson

Step 7: Adding and Configuring the OLE DB Destination

See Also

Concepts

Lookup Transformation