In this article, we are going to explain, How to perform SSIS Lookup Transformation in Full Cache Mode with an example. In order to use Full cache mode, we have to use the Cache Connection Manager along with SSIS Lookup. Before you start creating your package, Please refer the Lookup in SSIS article to understand the properties available in Lookup Transformation
Let us see the Lookup source table in our SQL Server, and its data before we start creating the package.
SELECT [Country] ,[State] ,[City] ,[UnitPrice] ,[ProductStandardCost] ,[SalesAmount] ,[TaxAmt] FROM [SSIS Tutorials].[dbo].[Lookup Transformation Source]
Configure SSIS Lookup Transformation in Full Cache Mode
STEP 1: In order to SSIS Lookup Full Cache Mode, first Drag and drop the Data Flow Task from the toolbox to control flow region and rename it as Lookup Transformation in Full Cache Mode.
Double click on it and it will open the data flow tab.
STEP 2: Drag and drop OLE DB Source from the toolbox to data flow region. Double click on OLE DB source in the data flow region will open the connection manager
From the above screenshot, you can observe that We selected [Lookup Transformation Source] table present in the [SSIS Tutorials] Database
STEP 3: Click on columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.
STEP 4: Add a Lookup Transformation to the Data Flow tab. Right click on Lookup Transformation to configure it.
In the General Tab, Under the connection type section change the connection type from OLE DB connection manager to Cache Connection Manager because we are using cache file (.caw).
Under “Specify how to handle rows with no matching entries”. Change the default Fail Component to Redirect rows to no match Output option. By changing this option, Unmatched rows will be redirected to No match output otherwise, Package will Fail.
STEP 5: Click on the connection tab to configure the Cache Connection Manager. Click on the new button to select the cache file in the local file system.
By clicking on the New button will open Cache Connection Manager Editor. Here, click on the Browse button to browse the local hard drive.
Here we selected the cache lookup.caw file which we created in Cache Transformation in SSIS article from file system.
Click Columns Tab in Cache Connection Manager Editor
Check the index position of the lookup column (Here it is Country Name). If it is 0 then change it to 1.
Click ok to finish configuring the Cache Connection Manager. Now click on the Column Tab in lookup Transformation Editor
STEP 6: Click on the Available Input columns. Next, drag the source column to the available lookup columns to create a join between the 2 data sets.
From the above, you can observe that a Country column in the source data will check against the [Country Name] column in the lookup table.
- If each row of the Country column in the source data match with any one row of the [Country Name] column in the lookup table then that row will be passed to the Matched Output.
- If each row of the Country column in the source data does not match with any one row of the [Country Name] column in the lookup table then that row will be passed to No Match Output.
Sometimes we may have some extra columns in the lookup table which we want to pass to the matched output. In these situations, we have check mark that column in Available lookup Columns as we did for Country Code column.
STEP 7: Drag two Excel Destinations on to the Data Flow tab and then drag the Lookup Transformation Output Arrow to one of the Excel Destination. A pop-up window Input Output Selection Window will appear prompting you to select the Input-Output type: either Lookup Match Output or Lookup No Match Output.
Select Lookup Match Output and click OK.
When you drag another Output Arrow to Excel Destination 1 then it will automatically allocate Lookup No Match Output to it.
STEP 8: Double click on the Excel Destination will open Excel Destination Editor. Click on the new button beside the Excel Connection manager to configure the Excel Connection Manager.
From the above screenshot, you can observe that we are browsing the Lookup Transformation Matched Rows.xls file located in our local hard drive as our destination file.
Now configure the table or worksheet if you already created in excel file. Or click on a new button to configure here.
Here we selected the Matched Rows as excel table
STEP 9: Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns.
STEP 10: Now Double click on the Excel Destination 1 to configure the No match output. Here we are going to select the Lookup Transformation Not Matched Rows.xls file located in our local hard drive as our destination file to store the Not Matched Row Data.
Now configure the table if you already created in excel file or click on a new button to configure here.
Here we selected the Unmatched Rows table as our destination excel table.
STEP 11: Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns. If they are not already mapped then drag the available Input columns to the Available Destination columns to join.
Right click on the SSIS Lookup Transformation Package in the Solution Explorer and select Execute Package.
From the above screenshot, you can observe that our SSIS Lookup Transformation Package has executed successfully. Let us open the Excel files to view the matched data and the unmatched data. First, Let us open the Lookup Transformation Matched Rows.xls File to see the Matched Rows.
From the above screenshot, you can observe that Country Code column is coming from the lookup table in cache.
Let us open the Lookup Transformation Not Matched Rows.xls File to see the Unmatched Rows.
Thank you for Visiting Our Blog