查閱轉換Lookup Transformation

「查閱」轉換會藉由聯結輸入資料行中的資料與參考資料集中的資料行來執行查閱。The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. 您可以使用查閱在相關資料表中存取以通用資料行中的值為基礎的其他資訊。You use the lookup to access additional information in a related table that is based on values in common columns.

參考資料集可以是快取檔案、現有的資料表或檢視、新資料表或 SQL 查詢的結果。The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. 「查閱」轉換會使用 OLE DB 連接管理員或快取連接管理員來連接到參考資料集。The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset. 如需詳細資訊,請參閱 OLE DB 連線管理員快取連線管理員For more information, see OLE DB Connection Manager and Cache Connection Manager

您可以利用下列方式設定「查閱」轉換:You can configure the Lookup transformation in the following ways:

  • 選取您要使用的連接管理員。Select the connection manager that you want to use. 如果想要連接到資料庫,請選取 OLE DB 連接管理員。If you want to connect to a database, select an OLE DB connection manager. 如果想要連接到快取檔案,請選取快取連接管理員。If you want to connect to a cache file, select a Cache connection manager.

  • 指定包含參考資料集的資料表或檢視。Specify the table or view that contains the reference dataset.

  • 藉由指定 SQL 陳述式產生參考資料集。Generate a reference dataset by specifying an SQL statement.

  • 指定輸入與參考資料集之間的聯結。Specify joins between the input and the reference dataset.

  • 從參考資料集將資料行加入至「查閱」轉換輸出。Add columns from the reference dataset to the Lookup transformation output.

  • 設定快取選項。Configure the caching options.

    「查閱」轉換支援下列 OLE DB 連接管理員的資料提供者:The Lookup transformation supports the following database providers for the OLE DB connection manager:

  • [SQL Server]SQL Server

  • OracleOracle

  • DB2DB2

    「查閱」轉換會嘗試在轉換輸入的值與參考資料集的值之間執行等聯結 (Equi-Join) The Lookup transformation tries to perform an equi-join between values in the transformation input and values in the reference dataset. (等聯結表示轉換輸入中的各資料列,必須至少符合參考資料集中的某個資料列)。如果無法執行等聯結,則「查閱」轉換會執行下列其中一項動作:(An equi-join means that each row in the transformation input must match at least one row from the reference dataset.) If an equi-join is not possible, the Lookup transformation takes one of the following actions:

  • 如果參考資料集中沒有相符的項目,則不會發生聯結。If there is no matching entry in the reference dataset, no join occurs. 根據預設,「查閱」轉換會將沒有相符項目的資料列視為錯誤;By default, the Lookup transformation treats rows without matching entries as errors. 不過,您可以設定「查閱」轉換,以將這些資料列重新導向至無相符結果輸出。However, you can configure the Lookup transformation to redirect such rows to a no match output.

  • 如果參考資料表中有多個相符項目,「查閱」轉換將只傳回查閱查詢所傳回的第一個相符項目。If there are multiple matches in the reference table, the Lookup transformation returns only the first match returned by the lookup query. 如果找到多個相符項目,則「查閱」轉換只會在已設定為將所有參考資料集載入至快取時才產生錯誤或警告。If multiple matches are found, the Lookup transformation generates an error or warning only when the transformation has been configured to load all the reference dataset into the cache. 在這種情況下,「查閱」轉換會在轉換填滿快取時偵測到多個相符項目時產生警告。In this case, the Lookup transformation generates a warning when the transformation detects multiple matches as the transformation fills the cache.

    聯結可以是複合聯結,表示您可以將轉換輸入中的多個資料行聯結至參考資料集中的資料行。The join can be a composite join, which means that you can join multiple columns in the transformation input to columns in the reference dataset. 轉換支援聯結任何資料類型的資料行,但 DT_R4、DT_R8、DT_TEXT、DT_NTEXT 或 DT_IMAG 除外。The transformation supports join columns with any data type, except for DT_R4, DT_R8, DT_TEXT, DT_NTEXT, or DT_IMAGE. 如需詳細資訊,請參閱 Integration Services 資料類型For more information, see Integration Services Data Types.

    通常參考資料集的值會加入至轉換輸出。Typically, values from the reference dataset are added to the transformation output. 例如,「查閱」轉換可從使用輸出資料行之值的資料表擷取產品名稱,然後將產品名稱加入至轉換輸出。For example, the Lookup transformation can extract a product name from a table using a value from an input column, and then add the product name to the transformation output. 參考資料表的值可取代資料行的值,或者可加入至新的資料行。The values from the reference table can replace column values or can be added to new columns.

    「查閱」轉換執行的查閱區分大小寫。The lookups performed by the Lookup transformation are case sensitive. 若要避免由於資料中的大小寫不同而造成查閱失敗,請先使用「字元對應」轉換將資料轉換成大寫或小寫,To avoid lookup failures that are caused by case differences in data, first use the Character Map transformation to convert the data to uppercase or lowercase. 然後在產生參考資料表的 SQL 陳述式中包含 UPPER 或 LOWER 函數。Then, include the UPPER or LOWER functions in the SQL statement that generates the reference table. 如需詳細資訊,請參閱字元對應轉換UPPER (Transact-SQL)LOWER (Transact-SQL)For more information, see Character Map Transformation, UPPER (Transact-SQL), and LOWER (Transact-SQL).

    「查閱」轉換具有下列的輸入和輸出:The Lookup transformation has the following inputs and outputs:

  • 輸入。Input.

  • 相符結果輸出。Match output. 相符結果輸出會處理轉換輸入中,至少符合參考資料集中一個項目的資料列。The match output handles the rows in the transformation input that match at least one entry in the reference dataset.

  • 無相符結果輸出。No Match output. 無相符結果輸出會處理輸入中沒有至少符合參考資料集中一個項目的資料列。The no match output handles rows in the input that do not match at least one entry in the reference dataset. 如果將「查閱」轉換設定為把沒有相符項目的資料列視為錯誤,則這些資料列會重新導向至錯誤輸出;If you configure the Lookup transformation to treat the rows without matching entries as errors, the rows are redirected to the error output. 否則,轉換會將這些資料列重新導向至無相符結果輸出。Otherwise, the transformation would redirect those rows to the no match output.

  • 錯誤輸出。Error output.

快取參考資料集Caching the Reference Dataset

記憶體中的快取會儲存參考資料集,並儲存編列資料索引的雜湊資料表。An in-memory cache stores the reference dataset and stores a hash table that indexes the data. 在完成封裝的執行之前,快取都會保留在記憶體中。The cache remains in memory until the execution of the package is completed. 您可以將快取保存至快取檔案 (.caw)。You can persist the cache to a cache file (.caw).

將快取保存至檔案後,系統就可以更快地載入快取。When you persist the cache to a file, the system loads the cache faster. 如此可改善「查閱」轉換和封裝的效能。This improves the performance of the Lookup transformation and the package. 請記住,當您使用快取檔案時,所使用的資料並不如資料庫中的資料新。Remember, that when you use a cache file, you are working with data that is not as current as the data in the database.

下列是將快取保存至檔案的其他優點:The following are additional benefits of persisting the cache to a file:

  • 在多個封裝間共用快取檔案。如需詳細資訊,請參閱 使用快取連線管理員以完整快取模式實作查閱轉換 Share the cache file between multiple packages. For more information, see Implement a Lookup Transformation in Full Cache Mode Using the Cache Connection Manager .

  • 使用封裝部署快取檔案,Deploy the cache file with a package. 接著就可以在多部電腦上使用資料。You can then use the data on multiple computers. 如需詳細資訊,請參閱 針對查閱轉換來建立及部署快取For more information, see Create and Deploy a Cache for the Lookup Transformation.

  • 使用「原始檔案」來源從快取檔案讀取資料,Use the Raw File source to read data from the cache file. 接著就可以使用其他的資料流程元件來轉換或移動資料。You can then use other data flow components to transform or move the data. 如需相關資訊,請參閱 Raw File SourceFor more information, see Raw File Source.

    注意

    快取連接管理員不支援使用「原始檔案」目的地所建立或修改的快取檔案。The Cache connection manager does not support cache files that are created or modified by using the Raw File destination.

  • 使用「檔案系統」工作在快取檔案上執行作業和設定屬性。Perform operations and set attributes on the cache file by using the File System task. 如需詳細資訊,請參閱 檔案系統工作For more information, see and File System Task.

    下列是快取選項:The following are the caching options:

  • 參考資料集是藉由執行「查閱」轉換之前使用資料表、檢視或 SQL 查詢而產生並載入快取。The reference dataset is generated by using a table, view, or SQL query and loaded into cache, before the Lookup transformation runs. 您可以使用 OLE DB 連接管理員來存取資料集。You use the OLE DB connection manager to access the dataset.

    此快取選項與 SQL Server 2005 Integration Services (SSIS)SQL Server 2005 Integration Services (SSIS)中用於「查閱」轉換的完整快取選項相容。This caching option is compatible with the full caching option that is available for the Lookup transformation in SQL Server 2005 Integration Services (SSIS)SQL Server 2005 Integration Services (SSIS).

  • 參考資料集是從資料流程中已連接的資料來源或從快取檔案產生,然後在「查閱」轉換執行之前載入至快取。The reference dataset is generated from a connected data source in the data flow or from a cache file, and is loaded into cache before the Lookup transformation runs. 您可以使用快取連接管理員或是快取轉換來存取資料集。You use the Cache connection manager, and, optionally, the Cache transformation, to access the dataset. 如需詳細資訊,請參閱 快取連線管理員快取轉換For more information, see Cache Connection Manager and Cache Transform.

  • 參考資料集是藉由在執行「查閱」轉換期間使用資料表、檢視或 SQL 查詢而產生。The reference dataset is generated by using a table, view, or SQL query during the execution of the Lookup transformation. 在參考資料集中具有相符項目的資料列,以及在資料集中沒有相符項目的資料列,都可以載入至快取。The rows with matching entries in the reference dataset and the rows without matching entries in the dataset are loaded into cache.

    超過快取的記憶體大小時,查閱轉換會自動從快取中移除最不常用的資料列。When the memory size of the cache is exceeded, the Lookup transformation automatically removes the least frequently used rows from the cache.

    此快取選項與 SQL Server 2005 Integration Services (SSIS)SQL Server 2005 Integration Services (SSIS)中用於「查閱」轉換的部分快取選項相容。This caching option is compatible with the partial caching option that is available for the Lookup transformation in SQL Server 2005 Integration Services (SSIS)SQL Server 2005 Integration Services (SSIS).

  • 參考資料集是藉由在執行「查閱」轉換期間使用資料表、檢視或 SQL 查詢而產生。The reference dataset is generated by using a table, view, or SQL query during the execution of the Lookup transformation. 不會將任何資料存入快取。No data is cached.

    此快取選項與 SQL Server 2005 Integration Services (SSIS)SQL Server 2005 Integration Services (SSIS)中用於「查閱」轉換的無快取選項相容。This caching option is compatible with the no caching option that is available for the Lookup transformation in SQL Server 2005 Integration Services (SSIS)SQL Server 2005 Integration Services (SSIS).

    Integration ServicesIntegration Services[SQL Server]SQL Server 的差異在於比較字串的方式。 and [SQL Server]SQL Server differ in the way they compare strings. 如果「查閱」轉換是設定為在執行之前將參考資料集載入至快取,則 Integration ServicesIntegration Services 會在快取中進行查閱比較。If the Lookup transformation is configured to load the reference dataset into cache before the Lookup transformation runs, Integration ServicesIntegration Services does the lookup comparison in the cache. 否則,查閱作業會使用參數化的 SQL 陳述式而由 [SQL Server]SQL Server 進行查閱比較。Otherwise, the lookup operation uses a parameterized SQL statement and [SQL Server]SQL Server does the lookup comparison. 這表示「查閱」轉換可能會根據快取類型,從相同的查閱資料表傳回不同數目的相符項目。This means that the Lookup transformation might return a different number of matches from the same lookup table depending on the cache type.

您可以透過 SSISSSIS 設計師或以程式設計方式設定屬性。You can set properties through SSISSSIS Designer or programmatically. 如需進一步詳細資訊,請參閱下列主題。For more details, see the following topics.

查閱轉換編輯器 (一般頁面)Lookup Transformation Editor (General Page)

使用 [查閱轉換編輯器] 對話方塊的 [一般] 頁面來選取快取模式、選取連接類型,及指定如何處理無相符項目的資料列。Use the General page of the Lookup Transformation Editor dialog box to select the cache mode, select the connection type, and specify how to handle rows with no matching entries.

選項。Options

完整快取Full cache
在查閱轉換執行之前產生參考資料集並將其載入快取。Generate and load the reference dataset into cache before the Lookup transformation is executed.

部分快取Partial cache
在查閱轉換執行期間產生參考資料集。Generate the reference dataset during the execution of the Lookup transformation. 將參考資料集中具有相符項目的資料列,以及在資料集中沒有相符項目的資料列載入到快取。Load the rows with matching entries in the reference dataset and the rows with no matching entries in the dataset into cache.

沒有快取No cache
在查閱轉換執行期間產生參考資料集。Generate the reference dataset during the execution of the Lookup transformation. 沒有資料會載入到快取。No data is loaded into cache.

快取連接管理員Cache connection manager
將查閱轉換設定為使用快取連接管理員。Configure the Lookup transformation to use a Cache connection manager. 只有在選取 [完整快取] 選項時,才能使用這個選項。This option is available only if the Full cache option is selected.

[無快取]OLE DB connection manager
將查閱轉換設定為使用 OLE DB 連接管理員。Configure the Lookup transformation to use an OLE DB connection manager.

指定如何處理無相符項目的資料列Specify how to handle rows with no matching entries
選取選項以處理沒有至少符合參考資料集中一個項目的資料列。Select an option for handling rows that do not match at least one entry in the reference dataset.

當您選取 [將資料列重新導向無相符結果輸出] 時,資料列會重新導向無相符結果輸出,且不當做錯誤處理。When you select Redirect rows to no match output, the rows are redirected to a no match output and are not handled as errors. 無法使用 [查閱轉換編輯器] 對話方塊的 [錯誤輸出] 頁面上的 [錯誤] 選項。The Error option on the Error Output page of the Lookup Transformation Editor dialog box is not available.

[指定如何處理無相符項目的資料列] 清單方塊中選取任何其他選項時,會將資料列當做錯誤處理。When you select any other option in the Specify how to handle rows with no matching entries list box, the rows are handled as errors. 可以使用 [錯誤輸出] 頁面上的 [錯誤] 選項。The Error option on the Error Output page is available.

外部資源External Resources

blogs.msdn.com 上的部落格文章: 查閱快取模式 Blog entry, Lookup cache modes on blogs.msdn.com

查閱轉換編輯器 (連接頁面)Lookup Transformation Editor (Connection Page)

使用 [查閱轉換編輯器] 對話方塊的 [連接] 頁面,來選取連接管理員。Use the Connection page of the Lookup Transformation Editor dialog box to select a connection manager. 如果您選取 OLE DB 連接管理員,也可以選取查詢、資料表或檢視來產生參考資料集。If you select an OLE DB connection manager, you also select a query, table, or view to generate the reference dataset.

選項。Options

當您在 [查閱轉換編輯器] 對話方塊的 [一般] 頁面上選取 [完整快取][快取連接管理員] 時,可使用下列選項:The following options are available when you select Full cache and Cache connection manager on the General page of the Lookup Transformation Editor dialog box.

[完整快取]Cache connection manager
從清單中選取現有的快取連線管理員,或按一下 [新增] 來建立新的連線。Select an existing Cache connection manager from the list, or create a new connection by clicking New.

新增New
使用 [快取連線管理員編輯器] 對話方塊來建立新的連線。Create a new connection by using the Cache Connection Manager Editor dialog box.

當您在 [查閱轉換編輯器] 對話方塊的 [一般] 頁面上選取 [完整快取][部分快取][無快取] 以及 [OLE DB 連接管理員] 時,可以使用下列選項。The following options are available when you select Full cache, Partial cache, or No cache, and OLE DB connection manager, on the General page of the Lookup Transformation Editor dialog box.

[無快取]OLE DB connection manager
從清單中選取現有的 OLE DB 連線管理員,或按一下 [新增] 來建立新連線。Select an existing OLE DB connection manager from the list, or create a new connection by clicking New.

新增New
使用 [設定 OLE DB 連接管理員] 對話方塊來建立新的連接。Create a new connection by using the Configure OLE DB Connection Manager dialog box.

使用資料表或檢視Use a table or view
從清單中選取現有的資料表或檢視,或按一下 [新增] 來建立新的資料表。Select an existing table or view from the list, or create a new table by clicking New.

注意

如果在 [查閱轉換編輯器][進階] 頁面上指定 SQL 陳述式,則該 SQL 陳述式會覆寫並取代此處所選取的資料表名稱。If you specify a SQL statement on the Advanced page of the Lookup Transformation Editor, that SQL statement overrides and replaces the table name selected here. 如需詳細資訊,請參閱 查閱轉換編輯器 (進階頁面)>。For more information, see Lookup Transformation Editor (Advanced Page).

新增New
使用 [建立資料表] 對話方塊建立新的資料表。Create a new table by using the Create Table dialog box.

使用 SQL 查詢的結果Use results of an SQL query
選擇此選項以瀏覽至預先存在的查詢、建立新查詢、檢查查詢語法,以及預覽查詢結果。Choose this option to browse to a preexisting query, build a new query, check query syntax, and preview query results.

建立查詢Build query
使用 查詢產生器,來建立要執行的 Transact-SQL 陳述式。Create the Transact-SQL statement to run by using Query Builder, a graphical tool that is used to create queries by browsing through data.

瀏覽Browse
使用此選項即可瀏覽至預先存在且已儲存為檔案的查詢。Use this option to browse to a preexisting query saved as a file.

剖析查詢Parse Query
檢查查詢的語法。Check the syntax of the query.

預覽Preview
使用 [預覽查詢結果] 對話方塊來預覽結果。Preview results by using the Preview Query Results dialog box. 此選項最多可顯示 200 個資料列。This option displays up to 200 rows.

外部資源External Resources

blogs.msdn.com 上的部落格文章: 查閱快取模式 Blog entry, Lookup cache modes on blogs.msdn.com

查閱轉換編輯器 (資料行頁面)Lookup Transformation Editor (Columns Page)

使用 [查閱轉換編輯器] 對話方塊的 [資料行] 頁面,即可指定來源資料表和參考資料表之間的聯結,以及從參考資料表中選取查閱資料行。Use the Columns page of the Lookup Transformation Editor dialog box to specify the join between the source table and the reference table, and to select lookup columns from the reference table.

選項。Options

可用的輸入資料行Available Input Columns
檢視可用的輸入資料行清單。View the list of available input columns. 輸入資料行是連接來源的資料流程中的資料行。The input columns are the columns in the data flow from a connected source. 輸入資料行和查閱資料行必須有相符的資料類型。The input columns and lookup column must have matching data types.

使用拖放作業,即可將可用的輸入資料行對應到查閱資料行。Use a drag-and-drop operation to map available input columns to lookup columns.

您也可以藉由在 [可用的輸入資料行] 資料表中反白顯示資料行,按下應用程式鍵,然後按一下 [編輯對應],使用鍵盤將輸入資料行對應到查閱資料行。You can also map input columns to lookup columns using the keyboard, by highlighting a column in the Available Input Columns table, pressing the Application key, and then clicking Edit Mappings.

可用的查閱資料行Available Lookup Columns
檢視查閱資料行清單。View the list of lookup columns. 查閱資料行是參考資料表中的資料行,您可在其中查閱符合輸入資料行的值。The lookup columns are columns in the reference table in which you want to look up values that match the input columns.

使用拖放作業,即可將可用的查閱資料行對應到輸入資料行。Use a drag-and-drop operation to map available lookup columns to input columns.

使用核取方塊即可在參考資料表中,選取要執行查閱作業的查閱資料行。Use the check boxes to select lookup columns in the reference table on which to perform lookup operations.

您也可以藉由在 [可用的查閱資料行] 資料表中反白顯示資料行,按下應用程式鍵,然後按一下 [編輯對應],使用鍵盤將查閱資料行對應到輸入資料行。You can also map lookup columns to input columns using the keyboard, by highlighting a column in the Available Lookup Columns table, pressing the Application key, and then clicking Edit Mappings.

查閱資料行Lookup Column
檢視選取的查閱資料行。View the selected lookup columns. 您的選擇會反映在 [可用的查閱資料行] 資料表的核取方塊選擇中。The selections are reflected in the check box selections in the Available Lookup Columns table.

查閱作業Lookup Operation
從清單中選取要在查閱資料行上執行的查閱作業。Select a lookup operation from the list to perform on the lookup column.

輸出別名Output Alias
輸入每個查閱資料行之輸出的別名。Type an alias for the output for each lookup column. 預設是查閱資料行的名稱;但是,您可以選取任何唯一的描述性名稱。The default is the name of the lookup column; however, you can select any unique, descriptive name.

查閱轉換編輯器 (進階頁面)Lookup Transformation Editor (Advanced Page)

使用 [查閱轉換編輯器] 對話方塊的 [進階] 頁面,即可設定部分快取並修改查閱轉換的 SQL 陳述式。Use the Advanced page of the Lookup Transformation Editor dialog box to configure partial caching and to modify the SQL statement for the Lookup transformation.

選項。Options

快取大小 (32 位元)Cache size (32-bit)
調整 32 位元電腦的快取大小 (以 MB 為單位)。Adjust the cache size (in megabytes) for 32-bit computers. 預設值是 5 MB。The default value is 5 megabytes.

快取大小 (64 位元)Cache size (64-bit)
調整 64 位元電腦的快取大小 (以 MB 為單位)。Adjust the cache size (in megabytes) for 64-bit computers. 預設值是 5 MB。The default value is 5 megabytes.

針對無相符項目的資料列啟用快取Enable cache for rows with no matching entries
將參考資料集中沒有相符項目的資料列存入快取。Cache rows with no matching entries in the reference dataset.

來自快取的配置Allocation from cache
指定針對在資料集中沒有相符項目的資料列所配置的快取百分比。Specify the percentage of the cache to allocate for rows with no matching entries in the reference dataset.

修改 SQL 陳述式Modify the SQL statement
修改用來產生參考資料集的 SQL 陳述式。Modify the SQL statement that is used to generate the reference dataset.

注意

在此頁面上所指定的選擇性 SQL 陳述式會覆寫並取代在 [查閱轉換編輯器] 的 [連接] 頁面上所指定的資料表名稱。The optional SQL statement that you specify on this page overrides and replaces the table name that you specified on the Connection page of the Lookup Transformation Editor. 如需詳細資訊,請參閱 查閱轉換編輯器 (連接頁面)>。For more information, see Lookup Transformation Editor (Connection Page).

設定參數Set Parameters
使用 [設定查詢參數] 對話方塊,即可將輸入資料行對應至參數。Map input columns to parameters by using the Set Query Parameters dialog box.

外部資源External Resources

blogs.msdn.com 上的部落格文章: 查閱快取模式 Blog entry, Lookup cache modes on blogs.msdn.com

另請參閱See Also

模糊查閱轉換 Fuzzy Lookup Transformation
詞彙查閱轉換 Term Lookup Transformation
資料流程 Data Flow
Integration Services 轉換Integration Services Transformations