SSIS Help: Cache and Lookup transform

{TOC}

Cache and Lookup example

Cache transform in SSIS

The Cache Transform transformation generates a reference dataset for the Lookup Transformation by writing data from a connected data source in the data flow to a Cache connection manager. The Lookup transformation performs lookups by joining data in input columns from a connected data source with columns in the reference database. You can use the Cache connection manager when you want to configure the Lookup Transformation to run in the full cache mode. In this mode, the reference dataset is loaded into cache before the Lookup Transformation runs.

This example show you how to use cache transform in ssis. 1.The first you need to create a new Data Flow task

2.Then you need to create a data source (Flat File source). Drag-and-drop Flat file Source component from the SSIS Toolbox.

3.Now configure new flat file connection manager and select data source file.

4.Source file must contains at least 2 fileds: id and other different data field. You can use source file similar to this

5.Press OK. The next step you need to drag-and-drop Cache Tranform to the Data Flow.

6.Open settings (two click on the component icon) and press "New" to create new Cache connection manager.

7.In opened window you need to select local cache file

8.Navigate to the Mappings tab and connect needed relationships

9.Press "OK". Congratulation! You have been successfully configured Cache transform.

Lookup transform in SSIS

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. The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset. Let`s see who Lookup transform wirking in ssis.

1.At first create a new Data Flow, drag-and-drop new Data Source (Flat file source) and configure a new connection manager.

2.Data file can be looked similar to this. It must be contains at least two fields: id and other data fields.

3.The next step you need to drag-and drop Lookup transform from the SSIS Toolbox and connect it to the data source.

4.Now open settings (double click on the icon). Select the Cache mode to "Full Cache" and Connection type to Cache connection manager.

Also you can handle rows with no matching entries. Open dropdown list and select "Redirect rows to not match output" in the bottom of the window.

5.Navigate to the connection tab and select from dropdown list "cache connection manager"

6.Now open columns tab and connect id fields together the choose at least one data column.

7.After all steps before this you need to create two Destinations (Flat file destination) and create referenced connection managers.

8.Connect it to the Lookup transform according to outputs to right places.

Congratulation! You have been successfully configured Lookup transform.