Professional SQL Server™ 2005 Integration Services

6.6. OLE DB Command

The OLE DB Command transformation 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. Now I don't know about you, but just hearing the "for each row" phrase in the context of SQL makes me 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—you'll actually be doing a few in this chapter. Just understand the impact and think about your use of 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 ...