I'm having problem to pass variable from a package to a another one.
I having 2 packages: the parent (parent.dtsx) use a Execute Package Task to execute the child package (child.dtsx).
In the parent package, I have a variable named var1 (data type String) with value=test
In my child package, I having a script task using the variable var1 to make a MsgBox (var1 is in readonly in the script):
MsgBox(CStr(Dts.Variables("var1").Value))
In this case, I having an error (cause the variable may no exist) but i can see the value of the variable in the MessageBox and the execute package task fail because of this error.
After I tried this solution: (Found in this Forum)
In the child package, I create the variable var1 (same type, no value). Then in the control flow, I right click on the background and select "package configurations".
I enable package configurations.
Then I add a new one. I change the configuration type to "parent package variable."
Then, in the specify configuration settings entry, I enter the name of the variable in the parent package.
On the following screen, I select the "value" property of the variable created in this package (Child package). At last, I give the configuration a name and hit finish.
If I execute my parent package, the result is no value in the MessageBox.
I also tried the same thing (with the package configuration) to pass a a variable with the connection string from a package to the connection of an another one (I set the connection string of the connection) and it doesn't work.
I have one package that executes 4 child packages (5 total). All 5 packages are set to log information using a connection string with a variable set at runtime for the location. The child packages use a parent variable to get the log connection string from the parent package.
The parent package logs fine, but a strange behavior occurs with the child packages.
The child packages all log data to the location used in the configuration file variable during runtime (like it's supposed to). However, I would get an error right before the first child package finishes execution saying path not found. Just for giggles, I created the folder I use in my development environment on the production environment. The error goes away, but the log file is created in that folder with no data in it. Subsequently, the log file with the data is created in the location set in the configuration file. Now I have two log files!!!!
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. Â So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent).
In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong?
What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing.
Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another.
Oh, and I think you could use a spellchecker on this message box. At least I could use one.
I have a parent package that calls a child package, when I run the parent package the child package picks up a variable value from the parent in a script task and runs fine, the problem I'm facing is when I run just the child package, the script task fails because it doesn't know about the parent variable. The dilemma I'm facing is in my child script task, if I add the parent variable to the ReadOnlyVariables list then the task fails because the parent variable doesn't exist when I just run the child. If I don't add the parent variable to the ReadOnlyVariabls list then if I try to use it then the task fails saying that the variable doesn't exist in the variables collection.
Is there a way to check for the existence of the parent variable, so when I just run the child package I don't get an error and I don't have to change my task every time I choose to run the child package only vs running the parent/child?
I have having trouble getting my hands around how to retrieve variables from a parent package. I read about the Environment variables and Configuration File at the parent package level and the Parent Package variable at the child level.
Here are my questions:
1. Can you only store/retrieve 1 variable in a config file at a time?
2. Does the child package have to define the variables and if so, do they have to be the same names as the parent package?
This seems so more more complex then the DTS2000 way of passing variables to and from packages.
Has someone managed to pass successfully a variable from a parent package to a child package? I€™ve tried a zillion permutations and I can€™t get it to work. The strange thing was that I was able to successfully do this with pre-RTM builds. Basically, what I am trying to do is:
The parent package has a variable, e.g. ExecutionID which I set using a script to System::ExecutionInstanceGUID. I verified that the variable is set correctly by dumping it to a SQL Server table. I created a child package variable with the same name. In the child package, I€™ve created a parent package configuration that points to the ExecutionID variable. I am trying to read the variable in a Derived Column Task in which I have a column linked to @ExecutionID. This doesn€™t work. Step-by-step instructions from someone who managed to concur this will be greatly appreciated.
Oh, I didn€™t have any luck hitting a breakpoint in a script task inside a child package with both in and out of process execution also.
I having a problem getting the child package variable values into a parent package. Here is the scenario;
I have parent package(Master) calling two child packages using execute package task. I have no problem to pass variable values from the parent to child using the parent configuration but not able to get the variable values from the child to parent. I am setting the package status in each child package based on certain conditions and determine the final status in a parent based on child package variable statuses. Does any body have any idea? I will appreciate your thoughts.Thanks..Mako
We have one main package from which 7 other child packages are called. We are using ParentPackage variables to assign values for variables and database connections.
While the values from ParentPackage variable get assigned to some of the packages properly, to others it doesn€™t assign the value.
For example: Except for one of the packages the database connection string gets assigned properly to all other packages.
Similarly, in another package one of the variables doesn€™t get assigned. In other packages it is assigned properly.
We have checked all the other property values and they are exactly the same.
We cannot make any head or tail of this erratic behavior.
Request ID Parent ID Account Name Addresss 1452 1254789 Wendy's Atlanta Georgia 1453 1254789 Wendy's Norcross Georgia 1456 1254789 Waffle House Atlanta Georgia
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate) 2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate) 3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
hi, i have two tables i want the identity value of the parent table to be inserted into the chile table here is my code,but i don't know why it isn't working ! protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;
I have a parent/child relationship in a relational database broken out like this: Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this: select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories) Each bottom-level category has a count attached to it. The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts). My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything). Thanks!
I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data:
This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id. Is this a good relational database design ? kindly suggest.
In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?
Hello all, I'm having a real hard time trying to figure this one out. I'm trying to create a sql query that selects both the parent name and it's children, but it's got to loop through all the record sets to populate a drop down as an end result.
I think I thought this out correctly: I have 2 tables
category relationship
tbl category cat_id //auto int cat_name // varchar
relationship r_id // auto int parent_id // int child_id // int
both the parent_id and child_id are associated with the cat_id in my category table I could have 1cars // this is parent 2 audi 3 bmw 4 chevy
Table data example
r_id parent_id child_id **************************** 1 1 15 2 1 16 3 1 17 4 2 55 5 2 56 etc... I want to select both the parent cat_name from category and also select the child cat_name where the parent_id = #
I can do it manaully like this select cat_name, cat_id, parent_id , child_id from category, relationships where child_id = cat_id and parent_id = 1
what is the best way to loop through all the parent ids to find child category? Could this be done in a stored procedure?
I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger. Thanks, James
William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward
with an unknown number of children for each parent.
Then I would like to be able to query against this view with something like this:
SELECT * FROM FamilyView Where Child2 = 'Peter'
I have no idea how to write the SQL for this View. Is it possible? Is this possible without using a cursor?
Below is my sample data of my table named "Groups"
Code: with Groups as ( select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup ) select * from groups;
Expected result:
Code: with ExpectedResult as ( select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all select '' as GroupName,'peoplesoft' as SubGroup union all select '' as GroupName,'mysql' as SubGroup union all select 'Microsoft' as GroupName,'Nokia' as SubGroup union all select '' as GroupName,'EShop' as SubGroup union all select 'IBM' as GroupName,'Meiosys' as SubGroup union all select '' as GroupName,'UrbanCode' as SubGroup ) select * from ExpectedResult;
some sample query to how to achieve this parent-child has the same table.
I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can.
First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day.
We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that.
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.
Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.
WITH Org_TREE AS ( SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
[code]...
I need to modify the query:
1. To display the records both from the Organization table and Organization_Log table. 2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
I am wondering if there is a way to insert one parent record with multi child records in one transaction? I am using dataset to update my database. I want to use transaction so if one record insert fails all the transctions rollback.
I think I might know the answer to this, but I wanted to see if any one has come up with a slick idea for enforcing this relationship. If I have two tables and one is dependent on the other (parent-child relationship), how can I enforce that every parent record has a corresponding child record? Here is a code example
USE tempdb
GO
CREATE TABLE dbo.Parent ( ParentId int NOT NULL )
ALTER TABLE dbo.Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentId)
CREATE TABLE dbo.Child ( ParentId int NOT NULL ,ChildId int NOT NULL )
INSERT INTO dbo.Parent VALUES (1) INSERT INTO dbo.Child VALUES (1,1) INSERT INTO dbo.Parent VALUES (2) INSERT INTO dbo.Child VALUES (2,2) INSERT INTO dbo.Child VALUES (2,1) INSERT INTO dbo.Parent VALUES (3)
SELECT p.ParentId, 'I SHOULD HAVE A DEPENDENT RECORD' FROM dbo.Parent p LEFT JOIN dbo.Child c ON p.ParentId = c.ParentId WHERE c.ParentId IS NULL
ParentId 3 should have a child record associated with it. I am assuming that these are my choices:
1) code all inserts to the parent table along with a insert to the child table and wrap those in a transaction
2) place a trigger for insert on the parent table that ensures that the child table is populated after data for the parent.
Here is the gotcha, we will be using a middle-tier data access layer (nhibernate or dlink) so .NET application developers will be creating the data modifications at the transactional level. Also there might be several ongoing ETLs that populate this schema as well, so multiple points of entry and seperate code blocks. I don't want to hide business logic within triggers.
I assume that all our coders are competent and could enforce this properly via code, but I know that mistakes happen. Has any one come across this situation and have a solution for enforcing the integrity of the schema with constraints?
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.
so if i give input say categoryid=1[This falls under main category-boxing] i need to get result as 1 boxing [main category] 4 mayweather [sub category] 5 tyson [sub category] 6 clinton woods [sub category]
if i give categoryid=5[Note:Tyson] result should be as 1 boxing [main category] 5 tyson [sub category]
dear friend,, i have two table.in my first table id is primarykey and in my second table id if foreign key. so my need i have to use one query to delete the primary key table values. so if i am deleting one id in primary key table the child in the second table has to be deleted automatically.if parent get deleted the child should get deleted automatically. so plese help me to do this please give me sample query please
Greetings,I just wanna know if anyone can tell me how to get all user definedtables in parent-then-child manner. I mean all the parents should belisted first and then childs.I dont think there is any direct way to do this, but i am not able toform any sort of query to achieve this.Any help will be greatly appreciated.TIA
