You are creating a SQL Server 2008 Integration Services (SSIS) instance which contains a
Data Flow task which reproduces some million rows for Company.com.
The data flow asks a match against a reference table which includes 50,000 rows including
every row would be utilized during the data flow in the reference table.
You should make sure that the data flow runs least time.
Which is the correct answer?
A.
You should choose a Lookup transformation along with the half cache option
B.
You should choose a Lookup transformation along with the Full cache option
C.
You should choose a default Lookup transformation
D.
You should choose a Lookup transformation along with the Default cache option
Explanation:
EXAM TIP
When trying to determine which transformation to use that brings more than one data source
together, remember that the Merge Join Transformation brings two sorted sources together
and matching rows together with either an Inner Join, a full outer Join, or a Left outer Join.
Merge Join can match more than one row across the join columns. This behavior is different
from that of the Lookup Transformation, which brings back only a single match across the
join columns of the Lookup table. The union All Transformation does not join rows together
but rather brings each row separately from the sources, stacking the rows together. The
number of rows in the output of union All is the combined row counts of all the inputs. The
Merge Transformation is similar to union All, except that the sources have to be sorted and
the sort position is preserved. You could also use a Lookup Transformation to associate data
from two sources. The Lookup can cache a table in memory and, through matching columns,
can return new columns to the data flow.
http://msdn.microsoft.com/en-us/library/bb895289.aspx
You can configure the Lookup transformation to use full cache mode and a Cache
connection manager. In full cache mode, the reference dataset is loaded into cache before
the Lookup transformation runs.