You design a Business Intelligence (BI) solution by using SQL Server 2008. You need to load data into your online transaction processing (OLTP) database once a week by using data from a flat file. The file contains all the details about new employees who joined your company last week. The data must be loaded into the tables shown in the exhibit. (Click the Exhibit button.) Employee.EmployeeID is an identity.
A SQL Server 2008 Integration Services (SSIS) package contains one data flow for each of the destination tables. In the Employee Data Flow, an OLE DB Command transformation executes a stored procedure that loads the Employee record and returns the EmployeeID value.
You need to accomplish the following tasks:
-Ensure that the EmployeeID is used as a foreign key (FK) in all child tables for the correct Employee record.
-Minimize the number of round trips to the database.
-Ensure that the package performs in the most efficient manner possible.
What should you do?
A.
Use a Lookup Transformation in each of the child table data flows to find the EmployeeID based on first name and last name.
B.
Store the EmployeeID values in SSIS variables and use the variables to populate the FK columns in each of the child tables.
C.
After the Employee table is loaded, write the data to a Raw File Destination and use the raw file as a source for each of the subsequent Data Flows.
D.
After the Employee table is loaded, write the data to a Flat File Destination and use the flat file as a source for each of the subsequent Data Flows.
Explanation:
Tip: “Ensure foreign key … Minimize round trips … manner” = “Raw File”http://technet.microsoft.com/en-us/library/ms141661.aspx
Raw File Destination
The Raw File destination writes raw data to a file. Because the format of the data is native to the destination, the data requires no translation and little parsing. This means that the Raw File destination can write data more quickly than other destinations such as the Flat File and the OLE DB destinations.
You can configure the Raw File destination in the following ways:
Specify an access mode which is either the name of the file or a variable that contains the name of the file to which the Raw File destination writes.
Indicate whether the Raw File destination appends data to an existing file that has the same name or creates a new file.
The Raw File destination is frequently used to write intermediary results of partly processed data between package executions. Storing raw data means that the data can be read quickly by a Raw File source and then further transformed before it is loaded into its final destination. For example, a package might run several times, and each time write raw data to files. Later, a different package can use the Raw File source to read from each file, use a Union All transformation to merge the data into one data set, and then apply additional transformations that summarize the data before loading the data into its final destination such as a SQL Server table.Raw File Source The Raw File source lets us utilize data that was previously written to a raw data file by a Raw File destination. The raw file format is the native format for Integration Services. Because of this, raw files can be written to disk and read from disk rapidly. One of the goals of Integration Services is to improve processing efficiency by moving data from the original source to the ultimate destination without making any stops in between. However, on some occasions, the data must be staged to disk as part of an Extract, Transform, and Load process. When this is necessary, the raw file format provides the most efficient means of accomplishing this task.
(McGraw-Hill – Delivering Business Intelligence with Microsoft SQL Server 2008 (2009))