Using OUTPUT Parameters with the OLE DB Source

Did you know that support for OUTPUT parameters was added to the OLE DB Source in SSIS 2012? So many features were added in 2012, this one is often overlooked. (I had completely forgotten about it until someone asked the other day…). This post has a quick walkthrough of how to use them.

For this example, we’ll be using the Production.uspGetList example from Books Online. It looks like this:

We see there are four parameters total:

  1. Product
  2. MaxPrice
  3. ComparePrice (OUTPUT)
  4. ListPrice (OUTPUT)

In the OLE DB Source, we create a connection manager and set the Data access mode to SQL command. In the SQL statement, we can mark parameters with a question mark (?), or hard code values by entering them directly. For this example, we’ll provide a value for MaxPrice, and use variables for the others. I’

 

image

We can map the parameters by clicking the Parameters… button.

image

Some important caveats:

  • You must use the parameter name, rather than the ordinal, when mapping the output parameters.
  • All OUTPUT parameter variables should be of type Object. This is because the provider will likely attempt to set the variable to NULL (DbNull) prior to executing the stored procedure, and only SSIS Object variables support DbNull values. This means you’ll need to cast the variable value to the appropriate type before you use it.
  • The OUTPUT value will be written to the variable when the data flow completes (i.e. in the PostExecute phase). This means you can use the variables in a control flow task after your Data Flow, but not within the same Data Flow.

 

image

2 Responses to “Using OUTPUT Parameters with the OLE DB Source”

  1. ArthurZ says:

    Thank you for the update Matt, but I am curious where would I be able to obtain the comprehensive list of all the changes that made into SSIS 2012? Also what became obsolete (in addition to the ActiveX, Execute 2000 DTS package tasks, etc.)?

    I am asking because back in fall of 2012 I needed to prepare a presentation on what is new and changed in SSIS 2012 which I delivered at three or four occasions thereafter and now it turned out there were more additions.

    Besides, my main source of the information was the link you shared above, but I has been always thinking there should be a MSDN article covering all the changes in details.

    • Matt Masson says:

      There were many, many changes that were considered "bug fixes" (600+) in 2012. We were careful to track breaking changes, but with so many changes going in, some of the smaller changes were missed. We got most of them in the BOL entry – http://msdn.microsoft.com/en-us/library/bb522534….

      Funny story about this feature – the developer who worked on it left the team shortly afterwards, so the change was sort of overlooked. I seem to remember doing the code review, but it completely slipped my mind when I put together the "What's New" presentations. I'm still not sure why it was implemented, as it's not a commonly requested feature… I assume the request came from a particularly persuasive Connect entry.

Leave a Reply

You must be logged in to post a comment.