SSIS Integration Services | Lookup transforms exercise | Get a list of all the dinosaur time periods which don&#39;t match



Software ==> SSIS Integration Services  (46 exercises)
Version ==> SSIS 2012 and later
Topic ==> Lookup transforms  (4 exercises)
Level ==> Relatively easy
Classroom ==> Introduction to SSIS  /  Fast track SSIS
Online ==> Introduction to SSIS  /  Fast track SSIS


In SQL Server Management Studio, open the SQL script in the above folder and execute it to get a table of geological periods:

The table of periods

The geological periods to which dinosaurs can belong - we hope.


Create a package called Dodgy Dinos, and within this create a data flow task which imports the list of dinosaurs from the Excel workbook in the above folder:

Dinosaurs source

Untick all of the columns apart from the first four.


Now add a lookup transform to look up the period for each dinosaur in the SQL Server table Periods, and report the errors:

Lookup transform

Send the non-matching rows down to a Union All transform, and use a data viewer to see the periods which don't match.


We won't spoil the surprise by revealing the anomalous data, but the two offending dinosaurs should show up in your data viewer.

When this is all working, close the package down.

This page has 0 threads Add post