Adding and Configuring the Lookup Transformations
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.
To add and configure the Lookup Currency Key transformation
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.
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.
On the Data Flow design surface, right-click the newly added Lookup transformation, click Rename, and change the name to Lookup Currency Key.
Double-click the Lookup CurrencyKey transformation.
In the Lookup Transformation Editor dialog box, in the OLEDB connection manager box, ensure that localhost.AdventureWorksDW is displayed.
In the Use a table or view box, type or select [dbo].[DimCurrency].
Click the Columns tab.
In the Available Input Columns panel, drag CurrencyID to the Available Lookup Columns panel and drop it on CurrencyAlternateKey.
In the Available Lookup Columns list, select the check box to the right of CurrencyKey.
Click OK.
Right-click the Lookup Currency Key transformation, click Properties.
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
In the Toolbox, drag Lookup onto the Data Flow design surface. Place Lookup directly below the Lookup CurrencyKey transformation.
Click the Lookup Currency Key transformation and drag the green arrow onto the newly added Lookup transformation to connect the two components.
On the Data Flow design surface, right-click the newly added Lookup transformation, click Rename, and change the name to Lookup DateKey.
Double-click the Lookup DateKey transformation.
In the Lookup Transformation Editor dialog box, in the OLEDB connection manager box, ensure that localhost.AdventureWorksDW is displayed.
In the Use a table or view box, type or select [dbo].[DimTime].
Click the Columns tab.
In the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey.
In the Available Lookup Columns list, select the check box to the right of TimeKey.
Click OK.
Right-click the Lookup Date Key transformation and click Properties.
Iin 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
Adding and Configuring the OLE DB Destination
See Also
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|