SSIS OLE DB Command Transformation
[[articles:SQL Server Integration Services (SSIS)|Integration Services]] uses transformations to manipulate data during an [[articles:Extract Transform Load (ETL)|ETL]] dataflow. Transformations can be used in Data Flow Tasks, between data source and destination components, or other transformations.
Creating data-driven [[articles:Extract Transform Load (ETL)|ETL process]]es is not unusual during a data warehouse implementation. Sometimes loops must be created, and differently parameterized commands are needed to be run in each cycle. The OLE DB Command Transformation is useful in this situation.
This transformation will execute a SQL statement (an OLE DB command) against databases for each record it takes as an input row. The values in the input columns can be mapped as parameters to the SQL statement.
The OLE DB Command transformation supports an input and a regular output. It supports an error output as well.
This transformation is considered as a non-blocking or synchronous one (the output reuses existing buffers from the input, each output row are in a 1:1 relation with an input row), but it must be handled very carefully: if the transformation gets a huge number of rows, it might execute them as separate commands which might hurt the performance of the destination database. Using this transformation for executing only SQL insert statements to a single table must be avoided - in this case, try a destination component instead.
In some less difficult cases, it might be worth to check whether the Foreach Loop Container can solve the task.
For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check [[articles:SSIS: List of Transformations|this Wiki article]].
See Also
See Also
- [[SQL Server Integration Services - List of TechNet Wiki Articles]]