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. Now you
get an order from your company CIO, you’re asked to develop a SQL Server 2008
Integration Services (SSIS) data flow. The data flow loads data to a SQL Server 2008
database. Using data from the rows in your data flow, you must make sure that the existing
records in the SQL Server database is updated by the data flow. Of the following data flow
components, which one should be used?
A.
SQL Server Destination should be used
B.
OLE DB Destination should be used
C.
OLE DB Command Transformation should be used
D.
Data Conversion Transformation should be used.
Explanation:
Msdn OLE DB Command Transformation
The OLE DB Command transformation runs an SQL statement for each row in a data flow.
For example, you can run an SQL statement that inserts, updates, or deletes rows in a
database table.
You can configure the OLE DB Command Transformation in the following ways:
* Provide the SQL statement that the transformation runs for each row.
* Specify the number of seconds before the SQL statement times out.
* Specify the default code page.
Typically, the SQL statement includes parameters. The parameter values are stored in
external columns in the transformation input, and mapping an input column to an external
column maps an input column to a parameter. For example, to locate rows in the
DimProduct table by the value in their ProductKey column and then delete them, you can
map the external column named Param_0 to the input column named ProductKey, and then
run the SQL statement DELETE FROM DimProduct WHERE ProductKey = ?.. The OLE DB
Command transformation provides the parameter names and you cannot modify them. The
parameter names are Param_0, Param_1, and so on. If you configure the OLE DB
Command transformation by using the Advanced Editor dialog box, the parameters in the
SQL statement may be mapped automatically to external columns in the transformation
input, and the characteristics of each parameter defined, by clicking the Refresh button.
However, if the OLE DB provider that the OLE DB Command transformation uses does not
support deriving parameter information from the parameter, you must configure the external
columns manually. This means that you must add a column for each parameter to the
external input to the transformation, update the column names to use names like Param_0,
specify the value of the DBParamInfoFlags property, and map the input columns that contain
parameter values to the external columns. The value of DBParamInfoFlags represents the
characteristics of the parameter. For example, the value 1 specifies that the parameter is an
input parameter, and the value 65 specifies that the parameter is an input parameter and
may contain a null value. The values must match the values in the OLE DB
DBPARAMFLAGSENUM enumeration. For more information, see the OLE DB reference
documentation. The OLE DB Command transformation includes the SQLCommand custom
property. This property can be updated by a property expression when the package is
loaded. For more information, see Integration Services Expression Reference, Using
Property Expressions in Packages, and Transformation Custom Properties. This
transformation has one input, one regular output, and one error output.