Add output params to data flow using OLEDB Command?

• Jason Marshall

  SSCommitted

  Points: 1791

  More actions

  July 30, 2010 at 1:41 pm

  #236122

  I have some data coming through the data flow, then I'm trying to run a sproc with two output params for each row using OLEDB command. I can't seem to figure out how to get the results from the sproc into the data flow.

  Ideas?

  Thanks,

  Jason

  ** sproc is called in OLEDB Command like this (no input params):

  EXEC dbo.thisSproc ? output, ? output

• Baskar B.V

  SSCrazy

  Points: 2823

  More actions

  July 30, 2010 at 3:25 pm

  #1200557

  Jason Marshall (7/30/2010)

  ---

  I have some data coming through the data flow, then I'm trying to run a sproc with two output params for each row using OLEDB command. I can't seem to figure out how to get the results from the sproc into the data flow.

  Ideas?

  Thanks,

  Jason

  ** sproc is called in OLEDB Command like this (no input params):

  EXEC dbo.thisSproc ? output, ? output

  I hope we can able to only pass INPUT parameters to the stored procedure using this OLE DB Command inside data flow task.

  The other ways to approach this.

  1. Using a Script component in the data flow task and use SQLClient namespace and its related class/methods to call a stored procedure and store the output values to SSIS variables.

  2. Perform this entire activity in control flow. Storing the result set in a variable of type object and looping though each record and calling the stored procedure with output parameter inside "Execute SQL Task".

  Hope this helps.

  [font="Arial"]BASKAR BV
  http://geekswithblogs.net/baskibv/Default.aspx
  In life, as in football, you won’t go far unless you know where the goalposts are.[/font]