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

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
    ... 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 ... In the Execute SQL Task Editor, I updated the General tab as follows: ... Set the Connection to my Audit Database connection, ...
    (microsoft.public.sqlserver.dts)
  • RE: SSIS Exec SQL Task Output Parm Value Not Updating Package Vari
    ... Instead of using an OUTPUT parameter in the sp, set the Execute SQL Task (on ... 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 ...
    (microsoft.public.sqlserver.dts)
  • RE: MS Query "Connections"
    ... handle that with my connection string. ... for using the stored procedure route is that it is faster. ... If you truly want to pass in the query string, ...
    (microsoft.public.excel.programming)
  • Re: How to navigate recursive stored procedures results
    ... connection is inherited by the next user, ... beforehand so any residual #Temp tables would be dropped. ... the identity is not "flow"ing to the SQL Server - ... union a SQL query and the results of a stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
Loading