Re: Passing Variables to another package

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 21:46:34 +0100

In message <10dfe5ebsos6ua8@corp.supernews.com>, Thomas Scheiderich
<tfs@deltanet.com> writes
>"Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message
>news:i6SFAOXEY31AFwlF@sqldts.com...
>> Comments inline-
>>
>> In message <10den4d5l1mr992@corp.supernews.com>, Thomas Scheiderich
>> <tfs@deltanet.com> writes
>> >I am a little confused here as to how this works.
>> >
>> >I have package A and it will call Package B (which will send data to Sql
>> >the results of the package that called it as well as email results and
>maybe
>> >some other things down the line). I have about 50 packages that will all
>> >call this package. I decided to do it this way so I don't need to edit
>each
>> >package whenever a change is made to the results handling routine.
>> >
>> >To make it easy, I want to pass rowsDone and rowsErrors from A to B and B
>> >will handle it from there.
>> >
>> >I assume to make this work I would have to have rowsDone and rowsErrors
>as
>> >Global variables in both packages.
>> >
>> >Then to pass the variables, I then need to go to package A, set up an
>> >"Execute Package" task. I then go to the "Outer Package Global
>Variables"
>> >tab to set the variables I am going to pass.
>> >
>> >The Outer Package Global Variables will have any Global variables I
>defined
>> >already in package B.
>> Almost, but not quite. The "Outer Package Global Variables" lists
>> variables in the outer package, also known as the parent package, or in
>> you description this is package A. You select the parent package
>> variables that you want to be passed into the child package, or package
>> B. If the variable does not exist in the child package it will be
>> created at run-time, but only for the duration of that execution.
>>
>
>But if it isn't there beforehand, what would be the point of creating it on
>the fly? I assume the code doesn't know about it then.

I didn't say it made sense, that is just what it does. One reason why
this can be significant is as a source or problems. Variable names are
case sensitive, so if you have MyVAR in the parent, but your code in the
child references MyVar then at run-time you will have both MyVAR and
MyVar, but of course you will not be using the value you may expect.

>
>> >In this case, that would be rowsDone and rowsErrors.
>> >This would override any setting I initially set in Package B.
>>
>> >
>> >I assume the variables would need to be set in the called package (B in
>this
>> >case) before defining them as Outer Package Global Variables. It could
>be
>> >that they don't need to be, as long as they are spelled correctly in both
>> >places before running the package.
>> Yes, and yes. As above if they doe not exist in B they will be created
>> on the fly, but the realistically the will exist otherwise how will be
>> able to use them when you develop it. Spilling *and* case are important,
>> as global variables are case sensitive.
>>
>>
>> >
>> >One of the confustions I have is this:
>> >
>> >What is the Inner Package Global Variables used for? I would assume it
>> >would be the same as the Global variables for the calling Package (A in
>this
>> >case). This doesn't seem to be the case, as mine is blank, but when I
>right
>> >click the design window, I get all my global variables. So I assume the
>> >Inner Package Global Variables are used for something other than Global
>> >variables between my Package A's tasks.
>> >
>> Inner variables is a strange beast, but it basically it allows you to
>> hard code within the context of the Exec Pkg Task values for the
>> existing global variables of child package. if you need to call the
>> child package twice, but give it a different value each time, then you
>> could use two Exec Pkg tasks, and use Inner variables to code those two
>> different values. Make sense?
>>
>
>Nope.
>
>Still makes no sense. As I said, the interesting thing seems to be that the
>Inner Globals seem to have the Parents Global variables in them and the the
>Outer Globals seem to have the child variables (as we talked about).
>

Inner gives you the option to select a variable that already exists in
the child and code a value, in the Exec Pkg Task UI, which gets passed
through to the selected child package variable at run-time.

Outer gives you the option to select a variable that exists in the
parent, and this entire variable, name, value (the global variable
object) is created (overwriting any existing var of that name) at
run-time.

>> >The other question is:
>> >
>> >Is there a way to send back a message/variable from B to A after B is
>> >finished - such as unable to write to Sql File or something that happened
>> >that I didn't expect?
>>
>> No. The Exec Pkg Task variable deal is one way only. If you want to pass
>> a result value back to the parent package you can -
>>
>> - log it to an external location, such as a SQL table and then read it
>> back in the parent, perhaps using the Dynamic Properties Task
>>
>> - use an ActiveX Script Task to load the child package, set any
>> variables, execute the child package, and then read the resultant child
>> global variables, thereby replacing the Exec Pkg Task.
>
>Not sure what you mean here.
>
>How do you read the childs global variable? I assume the parent won't get
>control until the child task is done.
>>

In script you can do something like this

...
oPkg.LoadFromSQLServer ...

' Set child var to value of parent var
oPkg.GlobalVariables("ChildVar").Value =
DTSGlobalVariables("ParentVar").Value

' Now execute the child package
oPkg.Execute

' Now pass child var back into parent, "get the result var"
DTSGlobalVariables("ParentVar").Value =
oPkg.GlobalVariables("ChildVar").Value 

-- 
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Relevant Pages

  • Re: Execute Package Task and setting its inner Package Global Vaiables
    ... Ok So you want to retrieve the values of child package Global Variables and use them in you Parent as well as setting child global ...
    (microsoft.public.sqlserver.dts)
  • Re: Execute Package Task and setting its inner Package Global Vaiables
    ... If you are using the ExecutePackage task then you can pass through a Parent package Global Variable to the child. ... Global variables values from the child package back in the parent. ...
    (microsoft.public.sqlserver.dts)
  • Re: Child Package Wont Fail Parent
    ... child package, not the NEWEST one. ... that the parent will unambiguously call that package. ... threads to ensure that it fails on first error, ...
    (microsoft.public.sqlserver.dts)
  • Child Package Wont Fail Parent
    ... parent package calls child package, a step in child fails, ... child completes anyway, parent doesnt fail. ... In child package / package properties / logging tab: ... Fail Package on First Error ...
    (microsoft.public.sqlserver.dts)
  • Re: A proposal for formal packages matching
    ... An instance of generic child package does not match as an ... instance of the generic parent. ... I meant exclusively visibility. ... A child package has parent's declaration ...
    (comp.lang.ada)