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

Todd, I was able to get it to work as you described, thanks for the tip!

Also, I was able to resolve my original problem and I thought it might be
helpful for anyone else who makes the same mistake I did.

The master package template contains two sequence containers. The first
container is for startup related tasks, including the creation of a record in
the audit table. The second sequence container contains the finish related
tasks, including updating the audit record with the completion datetime stamp
and successful processing indicator.

A package variable contains the key of the audit record created in the
startup step. My problem was that the package variable always contained the
default value in the finish task, not the key of the newly created record. I
changed the "RaiseChangeEvent" to True and added a breakpoint, but it would
never fire. That is why I assumed that the ouput parameter was not updating
the package variable.

I noticed (finally!) that I had not connected my first sequence container to
the second sequnce container on the Control Flow tab. I didn't believe this
was a big issue at first, since I considered the package variable as global
and it should hold its value across both tasks. Once I hooked the two
sequence containers together, everything worked!

I can think of a couple of possible reasons for this. First, since the
containers were not connected, there was no way to guarantee which order the
two sequence container tasks would execute. Second, the two events were
probably viewed as independent events. Therefore, the package variable was
reassigned the default value between execution of the separate sequence
container tasks.

The only remaining mystery for me is why the RaiseChangeEvent breakpoint did
not fire. Once I connected the two sequence containers, this event fired
normally and I was able to view the value of the package variable as it was
updated.

Everyone, thanks again for your assistance! I hope this helps someone else
avoid this situation.

--
Grande Applications


"Todd C" wrote:

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

  • 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: Using global variables
    ... This Query will return a rowset to you. ... Create a DTS package ... > Again I have within my package MicrosoftOLEDB connection to the NorthWind> database and Execute SQL Task ... >> INSERT TableA VALUES ...
    (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)
  • Re: create dynamic dts package
    ... In my reply I said that all this may not be necessary and you may be able to simply rebuild the Transform data Task in an existing package. ... Dim oConnProperty As OleDBProperty ... ' create package connection information ... Dim oConnection As Connection2 ...
    (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)
Loading