SSIS Help: Cache and Lookup transform

{TOC}

Cache and Lookup example

Cache transform in SSIS

The Cache"Cache TransformTransform" transformation generatescreates a reference dataset for the Lookup Transformation bythat will be used in cache, without writing onto disk. It writes data from a connected data source in the data flow to a Cache connection manager.

The Lookup transformation performs lookups by joiningjoins data in input columns from a connected data source with columns in the reference database. Youdataset canand useperforms the lookups.

Cache connection manager is most useful 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.

ThisThe example showbelow shows you how to use cache transform in ssis. 1.The firstSSIS.

1.First, you would need to create a new Data Flow taskTask

2.Then

1. Get youinto needthe toData Flow tab, and create a data sourcesource, (FlatLet's use Flat File source).Source as data source in our example. Drag-and-drop Flat fileFile Source component from the SSIS Toolbox.
   Toolbox into the Data Flow pane.

3.Now3.Now, let's configure new flatFlat fileFile connectionConnection managerManager by browsing onto the disk and selectselecting appropriate data source file.file that we already have prepared:

4.Source file must containscontain at least 2two fileds:fields. One will serve as an identifier, we will use the id field for that purpose, and otheranother differentis a data field.field with content. You can use source file similar to this

the one on the picture:

5.Press5.As you are done configuring the Flat File Source with all the appropriate fields definitions from the file, press OK. As Thethe next stepstep, you need towould drag-and-drop Cache Tranform tointo the Data Flow.Flow pane. Connect Flat File Source with Cache transform, so that all the metadata is mapped between two components.

6.Open6.Double click the Cache's component to open its settings (twoand click on"New" the component icon) and press "New"button to create new Cache connectionConnection manager.Manager.

7.In openedthe windowCache Connection Manager Editor that just opened, select "Use file cache" option and browse to the file you needcreated for the purpose of the exercise. Please, pay attention to selectthe localsensitive cachedata filewarning as protection levels of the packages do not apply to the Cache component.

8.Navigate

1. Now, open the Cache Transformation Editor ( by single clicking on the component) ,navigate to the Mappings tab and connect neededinput relationships
   and destination columns:

9.Press "OK". Congratulation!Congratulations! You have been successfully configured the 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.