RE: SSIS Exec SQL Task Output Parm Value Not Updating Package Vari

I do just about the same thing as you and mine works OK. Here are some tips:

Instead of using an OUTPUT parameter in the sp, set the Execute SQL Task (on
the General Tab) with a Result Set of "single row". Then in the Result Set
Row, create a new Result Set with the following: Result Name = "0", Variable
Name = <new variable>. Create your User::PackageExecKey here instead of in
the Variables toolbox area. I have had issues with creating the variable
first, then trying to 'hook into it' from the Execute SQL Task properties.

By the way:
my SP ends with "SELECT @@IDENTITY AS Load_ID" (the value of which ends up populating the ResultSet)
my ExecSQLTask uses ADO.Net.
the IsQueryStoredProcedure is set to FALSE, even though the SQLStatement is "EXEC usp_Get_Load_ID <parameter list>,..."

HTH
--
Todd Chittenden

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"GrandeApps" wrote:

I'm using the ADO.NET option because it made the most sense to me from a
parameter mapping standpoint. With stored procedures, the other connection
types use EXEC and ? parameter substitiution, with parameter names of 0,1,2
etc. With the ADO.NET option, parameter mapping uses the parameter names as
listed in the stored procedure, which makes the connection a little more
intuitive and not dependent on ordinal position.

Is there a reason why a stored procedure's OUTPUT parameter cannot be
accessed using the ADO.NET connector, via the parameter mapping tab? I
believe that is the intent of the "Direction" column when "OUTPUT" is
selected. If it is not possible using ADO.NET, I will switch to OLEDB or
some other type of connector. I didn't want to give up without at least
attempting to get a logical answer as to why this combination doesn't seem to
work.

--
Grande Applications


"GrandeApps" wrote:

I hope that someone can help me with my output parameter problem, as it has
been very frustrating so far. First some background:

I've created a Master Package that will be used as a template for developing
new SSIS packages. The package starts by logging some audit information into
a SQL table. At the end of the process, the created audit record will be
updated with a completion datetime stamp and a successful processing
indicator field.

I added a Sequence Container to the Control Flow tab. Inside this
container, I added an Execute SQL task. To create the audit record, I wrote
a stored procedure that accepts audit information via input parameters and
returns the identity key value back as an output parameter. I ran the stored
procedure in Management Studio and the stored procedure executes as intended,
returning the key of the new record.

In the Execute SQL Task Editor, I updated the General tab as follows:
1. Set the ConnectionType to ADO.NET
2. Set the Connection to my Audit Database connection, defined in Connection
Manager
3. Set the SQLStatement field to dbo.usp_APP_PackageAudit_Start
NOTE: This is the name of the stored procedure I'm using
4. Set the IsQueryStoredProcedure to True

In the Execute SQL Task Editor, I updated the Parameter Mapping tab as
follows:
1. Mapped variables, both User and System type, to the stored procedure
input parameters.
2. Mapped a Package Level user variable called "PackageExecKey" with the
following values
Direction: Output
DataType: Int32
Parameter Name: @parPackageExecKey
NOTE: This is the name of the stored procedures OUTPUT parameter, where the
key value is returned

When I run the package, the audit record is created and populated with all
of the correct information. However, my user variable "PackageExecKey" is
never updated with the key of the new record, via the output parameter. This
causes a problem when the package finishes, since I cannot update the audit
record with the completion datetime stamp and the successful processing
indicator.

If anyone can shed some light on this issue, please do! I know that there
may be some other connection types (e.g. OLEDB), but would prefer to stay
with the ADO.NET connection, since it uses the parameter names instead of the
? and ordinal position numbers. I find using the parameter names more
intuitive. If, however, that is the only way this will work I will be forced
to go that route.

Thanks for your help!
--
Grande Applications
.

Relevant Pages

  • SSIS Exec SQL Task Output Parm Value Not Updating Package Variable
    ... I hope that someone can help me with my output parameter problem, ... I've created a Master Package that will be used as a template for developing ... At the end of the process, the created audit record will be ... Set the Connection to my Audit Database connection, ...
    (microsoft.public.sqlserver.dts)
  • RE: SSIS Exec SQL Task Output Parm Value Not Updating Package Variable
    ... With stored procedures, the other connection ... listed in the stored procedure, which makes the connection a little more ... I've created a Master Package that will be used as a template for developing ... At the end of the process, the created audit record will be ...
    (microsoft.public.sqlserver.dts)
  • Invalid property size within Parameters
    ... stored procedure and the code. ... output parameter, compared the email to a table and matches an ID in another ... Dim getConn As New Connection ... Dim myConnection As SqlConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Invalid property size within Parameters
    ... values from stored procedures you use ExecuteScalar not ExecuteNonQuery. ... > stored procedure and the code. ... > Dim getConn As New Connection ... > 'Create output parameter ...
    (microsoft.public.dotnet.framework.aspnet)
  • RETURING XML AS A PARAMETER .NET
    ... I'm having real issues with returning data from an output parameter ... the input directly to the stored procedure and all seems to look fine. ... The sp simply takes in an xml document and returns a response xml in ... // check to see if the connection is still open ...
    (microsoft.public.sqlserver.xml)