You are a database developer and you have about two years experience in creating
business Intelligence (BI) by using SQL Server2008. Now you are employed in a company
named NaproStar which uses SQL Server2008. You work as the technical support.
According to the requirement of the company CIO, you design a SQL Server 2008
Integration Services (SSIS) data flow. The data flow inserts data from a source query to a
destination table. But now you must make sure that only those rows that do not already exist
in the destination table are inserted. So what should you do to accomplish this task? (choose
more than one)
A.
In order to accomplish this task, you can use the Fuzzy Lookup transformation
B.
In order to accomplish this task, you can use the Fuzzy Grouping transformation
C.
In order to accomplish this task, you can use the Merge transformation
D.
In order to accomplish this task, you can use the Lookup transformation
E.
In order to accomplish this task, you can use the Union All transformation
F.
In order to accomplish this task, you can use the Merge Join transformation
Explanation:
Lookup Transformation
The Lookup transformation performs lookups by joining data in input columns with columns
in a reference dataset. You use the lookup to access additional information in a related table
that is based on values in common columns. The reference dataset can be a cache file, an
existing table or view, a new table, or the result of an SQL query. The Lookup transformation
uses either an OLE DB connection manager or a Cache connection manager to connect to
the reference dataset. For more information, see OLE DB Connection Manager and Cache
Connection Manager Integration Services and SQL Server differ in the way they compare
strings. If the Lookup transformation is configured to load the reference dataset into cache
before the Lookup transformation runs, Integration Services does the lookup comparison in
the cache. Otherwise, the lookup operation uses a parameterized SQL statement and SQL
Server does the lookup comparison. This means that the Lookup transformation might return
a different number of matches from the same lookup table depending on the cache type.
Merge Join Transformation The Merge Join transformation provides an output that is
generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example,
you can use a LEFT join to join a table that includes product information with a table that lists
the country/region in which a product was manufactured. The result is a table that lists all
products and their country/region of origin. For more information, see Using Joins.
You can configure the Merge Join transformation in the following ways:
* Specify the join is a FULL, LEFT, or INNER join.
* Specify the columns the join uses.
* Specify whether the transformation handles null values as equal to other nulls.
Note: If null values are not treated as equal values, the transformation handles null values
like the SQL Server
Database Engine does.
This transformation has two inputs and one output. It does not support an error output.
Input Requirements
The Merge Join Transformation requires sorted data for its inputs. For more information
about this important requirement, see How to: Sort Data for the Merge and Merge Join
Transformations.
Join Requirements
The Merge Join transformation requires that the joined columns have matching metadata.
For example, you cannot join a column that has a numeric data type with a column that has
a character data type. If the data has a string data type, the length of the column in the
second input must be less than or equal to the length of the column in the first input with
which it is merged.