SSIS Exec SQL Task Output Parm Value Not Updating Package Variable

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

  • 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: 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 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)
  • RE: SSIS Exec SQL Task Output Parm Value Not Updating Package Vari
    ... The master package template contains two sequence containers. ... I noticed that I had not connected my first sequence container to ... Instead of using an OUTPUT parameter in the sp, set the Execute SQL Task (on ... With stored procedures, the other connection ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Global Variable problem - type mismatch
    ... But the problem is that the package still fails to run. ... of the properties within the DTS and run it from there then it works. ... > reference to the connection I have in my DTS. ...
    (microsoft.public.sqlserver.dts)