o < back
SSIS Help: Cache and Lookup transform
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.
you need to create
a new Data Flow task
you need tocreate a data source (FlatFile source).Drag-and-drop Flat fileSource 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.
opened window you need to select local cache file
- to the Mappings tab and connect
Congratulation! You have been successfully configured Cache transform.
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.