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.