title: Text output from a stored proc
body: <p>I have created a SQL stored procedure that produces the expected results when I call it from SQL Management Studio, it has one output parameter which is a message that tells me where the proc failed, if it fails.  Here is the top part of the proc</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[uspProgramUpdate] 
      @EmailFailMessage VARCHAR(100) OUTPUT
  </code></pre>
  
  <p>At the bottom of my stored procedure is where I set the value of the variable:</p>
  
  <pre><code>BEGIN CATCH
      ROLLBACK TRAN [CoS1]
      RAISERROR ('***An error occurred during processing, all transactions have been rolled back.  Correct the error and restart this process.***', 16, 1)
      SET @EmailFailMessage = '***An error occurred during processing, all transactions have been rolled back.  Correct the error and restart this process.***'
  END CATCH
  </code></pre>
  
  <p>What I am trying to do (that I can't figure out) is how to capture the text of EmailFailMessage so I can use that text in an email within SSIS.</p>
  
  <p>I have created an Execute SQL Task in SSIS which executes the stored procedure successfully on the SQL server.  I have successfully captured the RAISERROR event and I can see that in the output window within Visual Studio window when I run the SSIS package:</p>
  
  <pre><code>Error: 0xC002F210 at Execute SQL - run uspProgramUpdate, Execute SQL Task: Executing the query "EXEC uspProgramUpdate ?" failed with the following error: "***An error occurred during processing, all transactions have been rolled back.  Correct the error and restart this process.***". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
  Task failed: Execute SQL - run uspProgramUpdate
  </code></pre>
  
  <p>The SQLStatement property of the SSIS task is:</p>
  
  <pre><code>EXEC uspCoSSleepProgramUpdate ? OUTPUT
  </code></pre>
  
  <p>I have the following SSIS variable:</p>
  
  <pre><code>User::EmailFailMessage (string), value = asdf
  </code></pre>
  
  <p>and the parameter is mapped on the SSIS SQL task in Parameter Mappings as follows:</p>
  
  <pre><code>Variable Name| Direction | Data Type | Parameter Name | Parameter Size
  User::EmailFailMessage | Output | VARCHAR | @EmailFailMessage | -1
  </code></pre>
  
  <p>I have an email task that is being called and for that email task I have set:</p>
  
  <pre><code>MessageSource = @[User::EmailFailMessage]
  </code></pre>
  
  <p>When I run the SSIS package and receive the email, the message (body) of the email doesn't have the message from SQL after running the stored proc.  It does have the initial value set for the User::EmailFailMessage variable and I am expecting that to change, but it isn't.</p>
  
  <p>I have also tried, among other things, using 0 for the parameter name in the Parameter Mapping area of my Execute SQL Task.  Using that doesn't change the result.</p>