How to pass input and output parameters in Execute SQL Task?

• guptaopus

  SSCommitted

  Points: 1534

  More actions

  March 12, 2008 at 1:24 am

  #120459

  Hi,

  I have a SP which requires 3 inputs and retuns 3 outputs I want to store that output values to variables. I have declared 6 variables (3 for i/p & 3 for o/p) I am not able to map correctly please any body help me.

  my current settings are

  Connection: OLE DB

  SP: EXEC ODS_VLD_Header ?, ?, ?, ? output, ? output, ? output

  Resultset: None

  mapping: 0,1,2-i/p 0,1,2-o/p

  Please find above snapshots for more details

• XPSCodes-667952

  SSChasing Mays

  Points: 603

  More actions

  March 12, 2008 at 1:39 am

  #788412

  In parameter name, I think you have to mention the parameters from SP.

  For e.g

  if your SP is something like this

  ODS_VLD_Header (@Parm1 Int,@parm2 Int.. etc)

  You would need to mention @Parm1,@Parm2 under parameter name in parameter mapping. Also, you can get rid of the ?,? in the sql statement (shown in general.bmp)

  --------------------------------------------------------------------------------------
  Save our world, its all we have! A must watch video Pale Blue Dot[/url]

• Yihong

  Say Hey Kid

  Points: 674

  More actions

  March 13, 2008 at 11:27 am

  #789230

  I have the same problem, need to pass a input parameter to a stored procedure. I have the following in General setting:

  ConnectionType = OLE DB

  Connection = ServerName.DBName

  SQLSourceType = Direct input

  SQLStatement = Exec dbo.spName ?

  My parameter needs to get date from

  "select Max(date) from TableName"

  I got rid of the "?", but the Build Query says procedure expects parameter, which was not supplied.

  What did I miss? How to map the parameter? Thanks.

• XPSCodes-667952

  SSChasing Mays

  Points: 603

  More actions

  March 13, 2008 at 1:32 pm

  #789286

  Attached a sample with ADO.net and OLEDB samples. Hope this helps.

  You can check this link for more detailed information

  http://technet.microsoft.com/en-us/library/ms141003.aspx

  --------------------------------------------------------------------------------------
  Save our world, its all we have! A must watch video Pale Blue Dot[/url]

• Yihong

  Say Hey Kid

  Points: 674

  More actions

  March 19, 2008 at 11:49 am

  #791607

  Thanks for the example.

  I coded the max(date) in script task and saved it as a variable, then passed this variable to sp in Execute SQL Task.