Sunday, February 23, 2014

SSIS Interview : OLE DB Command Transform

The OLE DB Command Transform is a component designed to execute a SQL statement for
each row in an input stream. This task is analogous to an ADO Command object being created,
prepared, and executed for each row of a result set. The input stream provides the data for
parameters that can be set into the SQL statement that is either an in - line statement or a
stored procedure call. We don ’ t know about you, but just hearing the “ for each row ” phrase
in the context of SQL makes us think of another phrase — ” performance degradation. ” This
involves firing an update, insert, or delete statement, prepared or unprepared some unknown
number of times. This doesn ’ t mean there aren ’ t any good reasons to use
this transformation —.
Pay specific attention to the volume of
input rows that will be fed into it. Weigh the performance and scalability aspects during your
design phases against a solution that would cache the stream into a temporary table and use
set - based logic instead. To use the OLE DB Command Transform Task, you basically need to
determine how to set up the connection where the SQL statement will be run, provide the SQL
statement to be executed, and configure the mapping of any parameters in the input stream to
the SQL statement. Take a look at the settings for the OLE DB Command Transformation by
opening its editor. The OLE DB Command Transform is another component that uses the
Advanced Editor.

Label: SSIS Interview Questions

No comments:

Post a Comment