SSIS

Execute SQL Task: Comes under Control flow Batch Operation OLEDB Command Transformation: Comes under Data Flow Row by Row Operation

  There are 3 types of lookup cache modes. These are part of Lookup Transformation in SSIS. Full Cache Mode : This mode helps in querying the database before the data-flow task is executed. This constitutes a critical part of the pre-execution step of the data-flow task. In this mode, the entire data copy is copied from the table and stored into the lookup cache in SSIS. Partial Cache Mode : This mode helps in querying new rows in the database from different data sources. Here, the matched row data is cached into the SSIS lookup cache. If the lookup cache is full, the data is removed automatically from the cache based on the usage statistics of the existing rows to free up space for newly matched rows. No Cache Mode : Here, the data is not cached unless 2 subsequent sources come up with the same matched rows. Database queries are run to get matched data every time from the source.