Return Value in SSIS OLE DB Command

by

Problem

In SSIS, OLE DB Command is used as a part of Data Flow to run SQL codes to transform data. Is it possible to use one of the parameters as a return value?

Solution

Returning values in SSIS OLE DB Command task is possible but the limitation is you can only return one value and it needs to be the last parameter. In below example a variable@RV is defined so that the last “?” appears as the last “?” in the code.

DECLARE @RV TINYINT;
SET @RV =
  (
      select column from table where other_column = ?
  )

SELECT ? = @RV;

Hence the first ? acts as an input paramter (the one that is against “other_column”) and the second ? acts as the return value.