In this section, we build further upon the data flow created in the previous section. We will add an extra column using the Derived Column transformation and fetch extra data using the Lookup component.
The Derived Column transformation has the ability to either modify existing columns or add new columns to the buffer. You can open the editor by double-clicking the component.
In the editor, you can drag variables, parameters and existing columns to the expression. At the right, you have also a library of functions available. You can also choose if you want to replace existing columns or if you want to add a new column:
The Derived Column transformation is very powerful, but the one-line expression editor can be frustrating. Letís add a multicast and a data viewer to inspect the results:
With a lookup, we can match our current columns against a reference dataset and retrieve one or more columns if a match has been found. In our dataset, we have the StateProvinceID column. We are going to fetch the full name of the State/Province. Drag a Lookup transformation to the canvas and open the editor. In the first pane, there are a couple properties to set:
Letís set this option to ďRedirect row to no match outputĒ. In the next tab, you need to define the reference dataset. You can use the dropdown box to select a table, but just as with the source, a SQL statement is preferred. Select only the columns you need to make the match and of course the columns you want to return. We can use this T-SQL statement:
SELECT [StateProvinceID] ,[StateProvinceName] FROM [Application].[StateProvinces];
The last tab we need to edit is the Columns tab, where we specify how the matching will take place and which columns we want to return. You need to drag the key columns from the input columns to the key columns from the lookup columns. Then you can check each column from the lookup columns which you want returned; in our case the StateProvinceName.
Close the editor. When we now attach a multicast to the lookup component, we can choose which output we want:
Letís attach multicasts on both outputs, combined with data viewers so we can test the lookup component:
As you can see, the StateProvinceName column has been added to the buffer and there were no rows sent to the no match output.