Drag the Two Data Flow Tasks on Control Flow tab. Rename it as Data Flow Task Cache Connection Manager and Data Flow Task-Look Up Transformation for our reference. Connect the Two Data flow Tasks by using Precedence Constraints. This ensures the cache transformation executes before the execution of Look up Transformation. Otherwise Transformation will be fail. Please find the screen shot below.
Right click on the First Data Flow Task you add the cache Connection Manager and connect to the OLEDB Data Source then they will generate reference data set to Look up Transformation. Please find the screen shot below.
Right click on OLEDB Source, click on Edit, then connect to data source. If not exit create a new connection. Please find the screen shot below.
Click on New Button Find the next screen shot below.
Click on New Button and connect to Data Source. Please find the screen shot below.
Click on OK Button of connection Manager and then select the Table name from the OLEDB Source Editor and then click OK Button. Please find the screen shot below.
Next we need to configure the Cache Transform to connect to Cache Connection Manager and write the data from connected data source to the cache Connection Manager. Right click on the Cache Transform click on Edit and Configure the cache connection Manager. Please find the screen shots below.
Click on New Button. Please find the screen shot below.
Next Click on Columns Tab. Now you configure the cache connection manager to specify the column following. Which columns in reference data set are the Index columns. Look up transformation maps columns in input data source only index columns in the reference dataset. You must specify at least one column in as a reference column
We are going to specify the Product Id Column as Index column. This is the common column between in the input Data source in the reference Data set. We specify the Index as 1. Next click on General tab.
Next is the reference Dataset there is stored in Memory cache of package runs persist to the file. Saving the cache to file using the cache file and set upping the cache file to write the data to the cache connection managers and able to share data between multiple transformations in the different Packages.
You persist the cache by selecting Use File Cache and selecting the Cache File from Browse button or you can enter the filename.raw. The raw is the extension for Cache file. Please find the screen shot below.
Click ok Save settings and Check the mappings of Cache Transform .
Click on Control Flow Tab and click on Dataflow task look up transform. Add Oledb Source , Look up Transformation and OLEDB Destination. First connect to OLEDB Data Source and Look Up Transformation. Please find the screen shot below.
Right click on the OLEDB Source and click on edit and then select connection manager. If connection is not there and then create new connection . In this case I have already created Adventure works connection . I am going to select that connection . After that needed to be select Table name. In this case I am going to select Sales.SalesOrderDetails. Please find the screen shot below.
Now we have to configure Look up Transformation. Please find the screen shot below. For that right click on the Look up click on edit. Please find the screen shot below.
No comments:
Post a Comment