Export (0) Print
Expand All
12 out of 31 rated this helpful - Rate this topic

Adding and Configuring the Lookup Transformations

Updated: 5 December 2005

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

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.

  1. In the Toolbox, expand Data Flow Transformations, and then drag Lookup onto the design surface of 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, right-click the newly added Lookup transformation, click Rename, and change the name to Lookup Currency Key.

  4. Double-click the Lookup CurrencyKey transformation.

  5. In the Lookup Transformation Editor dialog box, in the OLE DB connection manager box, ensure that localhost.AdventureWorksDW is displayed.

  6. In the Use a table or view box, type or select [dbo].[DimCurrency].

  7. Click the Columns tab.

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

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

  10. Click OK.

  11. Right-click the Lookup Currency Key transformation, 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.

  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. On the Data Flow design surface, right-click the newly added Lookup transformation, click Rename, and change the name to Lookup DateKey.

  4. Double-click the Lookup DateKey transformation.

  5. In the Lookup Transformation Editor dialog box, in the OLE DB connection manager box, ensure that localhost.AdventureWorksDW is displayed.

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

  7. Click the Columns tab.

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

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

  10. Click OK.

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

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

Release History

5 December 2005

New content:
  • Added step to check locale properties.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.