Mapping Output Parameters in SSIS OLE DB Source Editor

Question

How can i map output parameter in OLEDB Soruce SSIS(BIDS 2008).In (BIDS 2012) we can specify the query parameter as input and output.

SET FMTONLY OFF;
EXEC  [dbo].[ProcessPingErrorAlert_KeyValue]
  @AlterId = ?,
  @Hour = ?,
  @Day = ?,
  @TraceId = ?,
  @IsAlert = ? OUTPUT

Please see below two picutres.

SSIS Package In BIDS 2008

SSIS Package in BIDS 2012

Answer 1

The SQL statement should be: EXEC ? = [dbo].[ProcessPingErrorAlert_KeyValue] ?, ?, ?, ?, ? OUTPUT .

Then edit SQL task and choose, in Parameter Mapping, @IsAlert as Output (check pic just for reference, variables are different). The first question mark is just for the return value (you have to declare that variable amd in Parameter Mapping). Please respect the order you have your question marks and its correspondence by 0, 1, 2, ...

For further information you can try to read this: https://sqlserverrider.wordpress.com/2014/08/31/execute-stored-procedure-with-input-and-output-parameters-and-return-status-in-ssis/

Answer 2

you need in Mapping to do the following:

Parameters: Put the name of the parameter like you named at the stored procedure. Variables: Put the name of the variable you want to store the parameter after the execution. Param Direcction: You have to select Output.

Before the Mapping, you will need to create a variable.