How to get the return/execution value of a package from a parent?

Hi there,

I'm trying to get the return value of a package. I see there is a ForcedExecutionValue property which I set using an expression (variable). What I'm executing are 2 packages, Package1 contains an Execute Package Task that calls Package 2. Package 2 contains a Script Task that sets the value of variable Max. I want to get the value of Max in Package 1 then how can I do this?

My first approach is toset the return value of Package 2 = Max and then I thought I could retrieve this value from Package 1 but I'm not able to do that yet.

Any thoughts?

Thanks for any help!The way I would approach this is to use a Script Task (surprise, surprise) to load and execute the package instead of the Execute Package Task. Your script has the ability to read the child packages variables after it has executed, thus allowing child variables to be passed back to the parent.
http://blogs.msdn.com/jamesk/archive/2005/12/21/506463.aspx

Alternatively, you can also have the child package set the parent's variable directly.
http://blogs.conchango.com/jamiethomson/archive/2005/03/17/1151.aspx|||Hi JayH,

Yes, I did the first approach and it worked fine. I'm loading the package from a Script Task and getting the Executables.Count and store this value in a local variable.

Thanks for the suggestion!.

Ricardo

How to get identity from parent Table and insert it into the child table

I have a table with the following schema
F_NAME L_NAME COURSE_ID COURSE_DESC
this table contains 10 records ,and I need to insert these records into 2
tables .TBSTUDENT and TBCOURSE.
in TBSTUDENT ,I have student_id which is an identity column and in TBCOURSE
,I have a foriegn key to this field. How can I isert 10 records from the
first table into TBSTUDENT AND TBCOURSE ,where I should first get the
identity generated from the first insert and use it in the second
insert(into TBCOURSE).
ThanksDECLARE @.myID int
INSERT INTO ......
VALUES ......
SELECT @.myID = SCOPE_IDENTITY()
Message posted via http://www.webservertalk.com|||I have to insert into two tables at the same time?
I didn't get it!!!
Thanks
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:6bd904766fc843f1bc532d3a3cd059f1@.SQ
webservertalk.com...
> DECLARE @.myID int
> INSERT INTO ......
> VALUES ......
> SELECT @.myID = SCOPE_IDENTITY()
> --
> Message posted via http://www.webservertalk.com

How to get identity from parent Table and insert it into the child

Is this a one time process?
Are you going to do this in SINGLE_USER mode?
AMB
"RayAll" wrote:

> I have a table with the following schema
> F_NAME L_NAME COURSE_ID COURSE_DESC
> this table contains 10 records ,and I need to insert these records into 2
> tables .TBSTUDENT and TBCOURSE.
> in TBSTUDENT ,I have student_id which is an identity column and in TBCOURS
E
> ,I have a foriegn key to this field. How can I isert 10 records from the
> first table into TBSTUDENT AND TBCOURSE ,where I should first get the
> identity generated from the first insert and use it in the second
> insert(into TBCOURSE).
> Thanks
>
>This is a process called by a DTS package and once at a time.
Thanks for your reply.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:628A3F8D-9DF9-4EED-B061-3C22A3A266DF@.microsoft.com...
> Is this a one time process?
> Are you going to do this in SINGLE_USER mode?
>
> AMB
>
> "RayAll" wrote:
>

How to get identity from parent Table and insert it into the child

Leaving aside for the moment your table schema, which seems a bit odd, This
is an example of where using Surrogate keys can be a bit trickier than using
meaningful keys... In order to do this is robust way, you must have some
mechanism (Other than the Surogate Identity Key) to "connect" the child
records to the right parent record.
Now to the table schema... regarding Your "Child" table, the one you have
named TBCOURSE, from the name, it would seem to contain Course data... But i
f
that's true, it makes no sense to have a FK in it that points to one row in
a
table which contains Students.. That implies that for each course, there is
but one and only one student, or some single student that plays some special
distinct role related to that course... SO, since that makes no sense, the
next logical question is
Exactly what do the rows in these tables represent? nd then...
What exactly are you trying to do with this insert ?
"RayAll" wrote:

> I have a table with the following schema
> F_NAME L_NAME COURSE_ID COURSE_DESC
> this table contains 10 records ,and I need to insert these records into 2
> tables .TBSTUDENT and TBCOURSE.
> in TBSTUDENT ,I have student_id which is an identity column and in TBCOURS
E
> ,I have a foriegn key to this field. How can I isert 10 records from the
> first table into TBSTUDENT AND TBCOURSE ,where I should first get the
> identity generated from the first insert and use it in the second
> insert(into TBCOURSE).
> Thanks
>
>Thanks for your reply.
No,The schema of the table is not odd ,because this is a buffer table which
I amusing in a DTS package to read records from a CSV file into this table
(and do the validation during transformation) and then from here I have to
distribute the records into the relavant tables.Dose it still seem to be
odd?
I made the chema myself ,because I wanted to simplify things here are the
schema of the 3 other tables I am trying to insert into.
**************CLIENT:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PE_CLI_FK]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PROGRAM_ENROLLMENT] DROP CONSTRAINT PE_CLI_FK
GO
CREATE TABLE [dbo].[CLIENT] (
[INTERNAL_ID] [numeric](12, 0) IDENTITY (1, 1) NOT NULL ,
<--this is the identity field
[CLIENT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
.....
) ON [PRIMARY]
GO
***********PROGRAM_ENROLLMENT:
CREATE TABLE [dbo].[PROGRAM_ENROLLMENT] (
[PROGRAM_ENROLLMENT_ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[INTERNAL_ID] [numeric](12, 0) NOT NULL , <--This is
foriegn key to the Client Table
...
) ON [PRIMARY]
GO
***********PERSONAL_INFO:
CREATE TABLE [dbo].[PERSONAL_INFO] (
[FIRST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[MIDDLE_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
...
[INTERNAL_ID] [numeric](12, 0) NOT NULL , <--this is also a foriegn
key to Client
...
[Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
******************
Here is what I'm trying to do:
I am trying to get each record from my buffer table and insert it into these
three tables,but before inserting into the Programe_enrolment and
Personal_info ,I have to have the InternalID of the Client Table,then I
should use the internalID in every other table I insert the rest of the
record.
For instance let's say this is my record in buffer table:
FirstName clientID UserName
Ray 1233 ray5531 ............
First I insert this into client and I get the internalID of for instance
1000 ,then I need to use this and insert other fields of this record into
Personal_Info and PROGRAM_ENROLLMENT.
Same process for all the other records in the buffer table.
Thanks for your time.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:A31711F6-1A30-4DDC-9741-5EF81D96CE62@.microsoft.com...
> Leaving aside for the moment your table schema, which seems a bit odd,
> This
> is an example of where using Surrogate keys can be a bit trickier than
> using
> meaningful keys... In order to do this is robust way, you must have some
> mechanism (Other than the Surogate Identity Key) to "connect" the child
> records to the right parent record.
> Now to the table schema... regarding Your "Child" table, the one you have
> named TBCOURSE, from the name, it would seem to contain Course data... But
> if
> that's true, it makes no sense to have a FK in it that points to one row
> in a
> table which contains Students.. That implies that for each course, there
> is
> but one and only one student, or some single student that plays some
> special
> distinct role related to that course... SO, since that makes no sense,
> the
> next logical question is
> Exactly what do the rows in these tables represent? nd then...
> What exactly are you trying to do with this insert ?
> "RayAll" wrote:
>|||Your table design is incomplete because you haven't declared natural keys on
these tables. Declare the proper keys and it's easy to do what you require.
See this example:
http://www.google.co.uk/groups?selm...%40giganews.com
On the other hand, replacing one surrogate key with another seems
particularly pointless anyway.
David Portas
SQL Server MVP
--|||Thanks for your reply.
Where am I replacing surrogate key with another one.I have only one
surrogate key in client and I am using this in other tables to maintanin a
link between client and other informations.What's wrong with this?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0t-dner_57mHRdTfRVn-vg@.giganews.com...
> Your table design is incomplete because you haven't declared natural keys
> on these tables. Declare the proper keys and it's easy to do what you
> require. See this example:
> http://www.google.co.uk/groups?selm...%40giganews.com
> On the other hand, replacing one surrogate key with another seems
> particularly pointless anyway.
> --
> David Portas
> SQL Server MVP
> --
>|||What's wrong is that IDENTITY should never be the *only* key of a table. By
definition IDENTITY should always be redundant and breaking that rule
destorys integrity. For example, you don't want duplicate clients with the
*same* Client_ID and Username. Declare the keys on the other columns and
your problem is solved. Take a look at the example I posted.
David Portas
SQL Server MVP
--|||Symantically ,there is a compund key there ,which I can rely on.However It
hasn't been created,but technically there is no record with same "ClientID"
and "Affiliate_ID".
Yes,your answer was really helpfu.I think that's the way to do so.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OZadncsqxMs9QNTfRVn-jw@.giganews.com...
> What's wrong is that IDENTITY should never be the *only* key of a table.
> By definition IDENTITY should always be redundant and breaking that rule
> destorys integrity. For example, you don't want duplicate clients with the
> *same* Client_ID and Username. Declare the keys on the other columns and
> your problem is solved. Take a look at the example I posted.
> --
> David Portas
> SQL Server MVP
> --
>

How to get identity from parent Table and insert it into the c

Your original post mentioned two tables as destinations for the data,
<<<
this table contains 10 records ,and I need to insert these records into 2
tables .TBSTUDENT and TBCOURSE.
These two tables TBSTUDENT and TBCOURSE, are the tables I was directing my
comments to... In your last post, you did not mention them at all... Instead
you describe THREE Tables that are destinations for the data.
<<<<<<
...wanted to simplify things here are the
schema of the 3 other tables I am trying to insert into.
CLIENT, PROGRAM_ENROLLMENT, and PERSONAL_INFO...
So now I am triply . In any event, in order to help, it is
necessary to have some idea about what the schema actually is, and what
abstraction (What real world biusiness "THING") each record in the table
respresents. Normally the name of the table helps with that, but when this
is noot obvious, then we have to guess based on what the joins in the code
that is uploaded for us to look at.
So: What exactly does an individual record in each of these three tables
represent?
And What does each row in the Import table represent ? (Is that the table
with schema F_NAME L_NAME COURSE_ID COURSE_DESC ')
Regards,
Charly
"RayAll" wrote:
> Thanks for your reply.
> No,The schema of the table is not odd ,because this is a buffer table whic
h
> I amusing in a DTS package to read records from a CSV file into this table
> (and do the validation during transformation) and then from here I have to
> distribute the records into the relavant tables.Dose it still seem to be
> odd?
> I made the chema myself ,because I wanted to simplify things here are the
> schema of the 3 other tables I am trying to insert into.
> **************CLIENT:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[PE_CLI_FK]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[PROGRAM_ENROLLMENT] DROP CONSTRAINT PE_CLI_FK
> GO
> CREATE TABLE [dbo].[CLIENT] (
> [INTERNAL_ID] [numeric](12, 0) IDENTITY (1, 1) NOT NULL ,
> <--this is the identity field
> [CLIENT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ......
> ) ON [PRIMARY]
> GO
>
> ***********PROGRAM_ENROLLMENT:
> CREATE TABLE [dbo].[PROGRAM_ENROLLMENT] (
> [PROGRAM_ENROLLMENT_ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
> [INTERNAL_ID] [numeric](12, 0) NOT NULL , <--This is
> foriegn key to the Client Table
> ...
> ) ON [PRIMARY]
> GO
> ***********PERSONAL_INFO:
> CREATE TABLE [dbo].[PERSONAL_INFO] (
> [FIRST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [MIDDLE_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ....
> [INTERNAL_ID] [numeric](12, 0) NOT NULL , <--this is also a foriegn
> key to Client
> ....
> [Timestamp] [timestamp] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ******************
> Here is what I'm trying to do:
> I am trying to get each record from my buffer table and insert it into the
se
> three tables,but before inserting into the Programe_enrolment and
> Personal_info ,I have to have the InternalID of the Client Table,then I
> should use the internalID in every other table I insert the rest of the
> record.
> For instance let's say this is my record in buffer table:
> FirstName clientID UserName
> Ray 1233 ray5531 ............
> First I insert this into client and I get the internalID of for instance
> 1000 ,then I need to use this and insert other fields of this record into
> Personal_Info and PROGRAM_ENROLLMENT.
> Same process for all the other records in the buffer table.
>
> Thanks for your time.
>
>
>
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:A31711F6-1A30-4DDC-9741-5EF81D96CE62@.microsoft.com...
>
>I'm sorry if I made you .However I think I am getting close to wht I
want(I will discuss it later)
Forget about the first post.
Ok,
Client: represents a client of our affiliates it says that this clientID
,with this AfflicateID (unique key on this two)has this Internal_ID
.Internal_ID now represents this person in all other tables.
so far so good?
Personal_Info :represents the personal information about this
guy(Unfortunately it is in another table and it's related to the client by
internal_ID) ,information like spouse name,last date he kissed somebody and
bluh bluh bluh is stored in this table
Program_enrolment :contains information about programs that client has
participated so far,this table contains programe_code ,program_startdate and
bluh bluh bluh and again it's related to the client table using Internal_ID
Is that clear so far?
Now ,each record in CSV file which I later import it to a buffer table
contains all these information in one line.for instance it says that this
guy who belongs to this affiliate and has enroled in this program and his
wife name is "Eve" and last time he kissed somebody was yesterday and bluh
bluh bluh.so for inserting this record into our database I need to sperate
each records into different portions .First I have to insert some fields
into client Table and get our own internalID ,because I need them later in
other Tables.I am not using only surrogate key in client Table because
"ClientID and Afflicate Id" is also unique.
based on this I have some idea ,I'm not sure if it works( I got the idea
from David's post,right above us)
Do you think there is something wrong with this schema up to now?
I insert into the client table ,it gives the record an identity(Internal_ID)
(which I don't care for now).
then I join my import Table with Client Table based on "ClientID and
Afflicate Id" and extract Internal_ID and insert it into two other tables.
How about this?
Thanks
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:8D5178C2-0D79-4B11-A817-59B840C86B04@.microsoft.com...
> Your original post mentioned two tables as destinations for the data,
> <<<
> this table contains 10 records ,and I need to insert these records into 2
> tables .TBSTUDENT and TBCOURSE.
> These two tables TBSTUDENT and TBCOURSE, are the tables I was directing my
> comments to... In your last post, you did not mention them at all...
> Instead
> you describe THREE Tables that are destinations for the data.
> <<<<<<
> ...wanted to simplify things here are the
> schema of the 3 other tables I am trying to insert into.
> CLIENT, PROGRAM_ENROLLMENT, and PERSONAL_INFO...
> So now I am triply . In any event, in order to help, it is
> necessary to have some idea about what the schema actually is, and what
> abstraction (What real world biusiness "THING") each record in the table
> respresents. Normally the name of the table helps with that, but when
> this
> is noot obvious, then we have to guess based on what the joins in the code
> that is uploaded for us to look at.
> So: What exactly does an individual record in each of these three tables
> represent?
> And What does each row in the Import table represent ? (Is that the table
> with schema F_NAME L_NAME COURSE_ID COURSE_DESC ')
> Regards,
> Charly
>
> "RayAll" wrote:
>|||See InLine...
"RayAll" wrote:

> I'm sorry if I made you .However I think I am getting close to wht
I
> want(I will discuss it later)
> Forget about the first post.
> Ok,
> Client: represents a client of our affiliates it says that this clientID
> ,with this AfflicateID (unique key on this two)has this Internal_ID
> ..Internal_ID now represents this person in all other tables.
> so far so good?
No, not good already... If this table represents a client... then unique key
should be on CLientID by itself, not ClientID and AffiliateID... The fact
that your key is on CLientID AND AffiliateID means that mutiple records can
exist for the same ClientID, so this table CANNOT represent Clients, it must
represent ASSOCIATIONS (of what type I don't know) between CLients and
Affiliates. And if it does, then there has to be another two tables
somewhere - one whose records actually do represesnt CLIENTS, and another
table whose records represent AFFILIATES...|||The reason we have Client Table is that each client might belong to
different affiliates ,so there is a single peice of info kept in
Personal_INFO and here in client Table we say this client belongs to this
affiliate and this is our internal_ID if we want more information.
Dose that make sense?
Thanks,
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:B80EA846-73D1-4FDC-81DA-987A1D75A78D@.microsoft.com...
> See InLine...
> "RayAll" wrote:
>
> No, not good already... If this table represents a client... then unique
> key
> should be on CLientID by itself, not ClientID and AffiliateID... The fact
> that your key is on CLientID AND AffiliateID means that mutiple records
> can
> exist for the same ClientID, so this table CANNOT represent Clients, it
> must
> represent ASSOCIATIONS (of what type I don't know) between CLients and
> Affiliates. And if it does, then there has to be another two tables
> somewhere - one whose records actually do represesnt CLIENTS, and another
> table whose records represent AFFILIATES...
>|||Do you see that your CLIENT Table is really a CLIENT-AFFILIATE ASSOCIATION
("belonging") Table, and that your Personal_INFO table is (should be) the
CLient table ?
This is because each record in the CLIENT able represents, not a client, but
an association between a Client and an Affiliate...
And does each record in the Personal_INFO table actually represent a Client?
If it did, the Primary key in the Personal_INFO table woul be ClientID, not
InternalID... The way you have it is entirely possible that 2 records in the
CLIENT Table, for the SAME CLient, (with same ClientD) could "point" to 2
DIFFERENT rows in the Personal_INFO table...
So is there a table in your system that actually IS a Client Table? i.e.,
it has CLientID as it's unique Primary Key, and there is one and only one ro
w
per client in it?
"RayAll" wrote:

> The reason we have Client Table is that each client might belong to
> different affiliates ,so there is a single peice of info kept in
> Personal_INFO and here in client Table we say this client belongs to this
> affiliate and this is our internal_ID if we want more information.
> Dose that make sense?
> Thanks,
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:B80EA846-73D1-4FDC-81DA-987A1D75A78D@.microsoft.com...
>
>|||See inline please

> Do you see that your CLIENT Table is really a CLIENT-AFFILIATE ASSOCIATION
> ("belonging") Table, and that your Personal_INFO table is (should be) the
> CLient table ?
Yes,you are right ,but you should know that there are some semantics which
is beyond this discussion.I think the naming for Client is not good actually
,but the role is palying is right.

> And does each record in the Personal_INFO table actually represent a
> Client?
yes,it dose

> If it did, the Primary key in the Personal_INFO table woul be ClientID,
> not
> InternalID... The way you have it is entirely possible that 2 records in
> the
> CLIENT Table, for the SAME CLient, (with same ClientD) could "point" to 2
> DIFFERENT rows in the Personal_INFO table...
The internal_ID of the Personal_INFO is not an identity ,it's getting its
value from InternalID of Client and there is a one-to-one relationship
between them,how would it be possible to have multiple client_ids there?can
you give me an example?

> So is there a table in your system that actually IS a Client Table? i.e.,
> it has CLientID as it's unique Primary Key, and there is one and only one
> row
> per client in it?
>
No,that's the only table represeting the Client.
Thanks
> "RayAll" wrote:
>|||<The internal_ID of the Personal_INFO is not an identity ,it's getting its
<value from InternalID of Client and there is a one-to-one relationship
<between them,how would it be possible to have multiple client_ids there?can
<you give me an example?
Yes, the PK in the CLIENT table is NOT CLientID, it is ClientID AND
AffiliateID.
As you said, some clients are associated with more than one Affiliate, in
that case, won't there be multiple rows in CLIENT with the same ClientID,
(one for each of the Affiliates the client isassociated with)?
And each of these rows will have it's own unique InternalID, no?
So then, Do these Multiple InternalIDs (ALL FOR THE SAME CLIENT, right?), do
they not point to different rows in the Personal_INFO table'
Multiple rows in Personal_INFO for the SAME CLIENT, then, right ?|||
> Yes, the PK in the CLIENT table is NOT CLientID, it is ClientID AND
> AffiliateID.
> As you said, some clients are associated with more than one Affiliate, in
> that case, won't there be multiple rows in CLIENT with the same ClientID,
> (one for each of the Affiliates the client isassociated with)?
> And each of these rows will have it's own unique InternalID, no?
yes,right
> So then, Do these Multiple InternalIDs (ALL FOR THE SAME CLIENT, right?),
> do
> they not point to different rows in the Personal_INFO table'
yes,right.because in personal_info the key is Internal_ID (which is coming
from Client) and Education_level (which is another field) that make the rows
unique.I think the second field is the key.
> Multiple rows in Personal_INFO for the SAME CLIENT, then, right ?
yes but with different education_level.

>
Thanks for following this up.|||So Personal Infp also has an education_Level as part of the key as well'
That adds another twist to the puzzle... Bottom line, you really, REALLY
need to read a book or article or something about database design, and
database normalization... REGARDLESS Of the level of control you might have
over the database, or opportunity to redesign it properly.
The current design schema of your database is incredibly bad, and
uncorrectted, it will produce a nightmare of coding problems, data
inconsistency issues and maintenance headaches... The more you know about wh
y
and how it is messed up, the better you will be prepared to deal with these
issues, as they arise, even if you have no authority or approval from client
to correct them..
Good Luck !!
Charly
"RayAll" wrote:

>
> yes,right
> yes,right.because in personal_info the key is Internal_ID (which is coming
> from Client) and Education_level (which is another field) that make the ro
ws
> unique.I think the second field is the key.
> yes but with different education_level.
>
> Thanks for following this up.
>
>|||Thanks for your help.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:D2DBA4FE-90AA-48A7-8B1C-E73367A61610@.microsoft.com...
> So Personal Infp also has an education_Level as part of the key as well'
> That adds another twist to the puzzle... Bottom line, you really,
> REALLY
> need to read a book or article or something about database design, and
> database normalization... REGARDLESS Of the level of control you might
> have
> over the database, or opportunity to redesign it properly.
> The current design schema of your database is incredibly bad, and
> uncorrectted, it will produce a nightmare of coding problems, data
> inconsistency issues and maintenance headaches... The more you know about
> why
> and how it is messed up, the better you will be prepared to deal with
> these
> issues, as they arise, even if you have no authority or approval from
> client
> to correct them..
> Good Luck !!
> Charly
> "RayAll" wrote:
>