Using Variable/Expression To Log With Parent/Child Packages
Aug 17, 2006
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!!!!
I have a fairly simple SSIS project that has nested parent-child packages. I am trying to find the best way to manage the connections strings so as to make the package portable across machines and environments. Currently there is one "master" package which calls 6 child packages. 1 of these child package calls 3 child packages of its own.
For the database connections, I've settled on creating a standardized .dtsConfig file for each server/login. This is a relatively small number (intially 8) that I don't expect to grow much.
I've taken a different approach for the file-system connections used by Execute Package components that call the child packages. For each package that has child packages, I store all the connection strings (paths) to the child packages in a single .dtsConfig file. This works well for the top-level "master" package where I can pass in the .dtsConfig file (that has the paths to the child packages) as a run-time option in the Execute Package Utility.
However, this approach seems to fall apart for the 2nd generation package that in turn call 3rd generation packages because I don't know how to get the .dtsConfig file (with the 3rd generation .dtsx package paths) path to this downstream dtsx package.
Though I'm sure there are others, the only two solutionsI can think of now are (a)don't nest packages beyond 1 parent/child relationship -- not really an option or (b)Store the path of .dtsConfig files for each .dtsx package as an environment variable on each machine. This option is unappealing because it would require adding an environment variable for every .dtsx package that has child packages. I don't think it would take long for this to grow into a large number, that would make managing environment variables cumbersome.
So far my experience with SSIS has been that there was a simple solution for each scenario I had. So this hoop jumping I'm going through seems to indicate I am just missing something.
I have a Business Intelligence project containing 4 packages. One of the packages is a parent package that just runs the other 3 packages sequentially (passing in variables via Package Configurations).
When I set the properties of the Execute Package tasks in the parent package, it seems that I can only select SQL Server or File System locations for the child packages.
What I want to do is for the parent to run the packages in the Visual Studio Project so that I can debug the whole process. I don't want to be bothered deploying the child packages so that I can run them from the parent, at least not while I am debugging the whole process.
How do I get the children to run within Visual Studio as well as the parent? I can't for the life of me find anything on the MSDN about this.
I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.
Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?
Has anyone had experience of using Parent/Child packages while enlisting them in Transactions. I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.
The parent package essentially calls three child packages. In each child package there are multiple DFT's that import and transform data into SQL Server. All data must be imported or not at all. Therefore I created a FELC container into which three Exec child package tasks were placed. The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported. Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.
So initially we had in terms of container hierarchy for the Trans Option property: Parent package Supported FELC for calling child packages Required Task execute child package Supported Child package Suppored Tasks Suppored
Looking at this more closely we thought that we would need Parent package Supported FELC for calling child packages Required Task execute child package Required Child package Required Tasks Suppored
for it to work. However, the latter now gives us failures with error messages on the tasks on the child packages. [Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.
Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error. These tasks also have Event handlers.
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.
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
I've created an SSIS package that uses parent package variables at several steps in the data flow. However, those parent package variables are only visible during runtime, making debugging the package practically impossible. Let me give you a simplified example:
In the parent package, I have a string variable named "sqlLookup" that looks like this:
"SELECT * from tblTest WHERE city = " + @city
Also in the parent package is a variable named "city" which I can set to the name of the city that I want to query on. This dynamically updates the sqlLookup variable, which is being evaluated as an expression.
In the child package, I have an OLE DB Source control which is using the "sqlLookup" variable for its query. I have set up my parent package variable configuration, and it works when I run the package.
The problem is this...when I open the child package, I get an error on the OLE DB Source control using the parent package variable, "Command text was not set for the command object". Presumably, this is because the parent package variable is only available during run-time, and not at design time. And, if metadata changes (which it has), I can't get into the OLE DB Source control to edit it, because it throws the error.
So, my question is this: are there any workarounds for this problem? Is there a better way to do this? This seems like such an obvious problem that I'm wondering if I either missed a step somewhere, or if I'm just doing it the wrong way.
Hi everybody, I moved my packages to the new machine, and have problem with parent variables. When child package tries to get parent variable value I get an error:
Information: 0x40016042 at LoopPackage: The package is attempting to configure from the parent variable "MAIN_SesId".
Warning: 0x80012028 at LoopPackage: Process configuration failed to set the destination at the package path of "Package.Variables[User::MAIN_SesId].Properties[Value]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable.
I am running a parent SSIS package (running sp2, 9.0.3042) that calls several child packages.
On our development server, we now cannot run this because we get 1 or more of these errors:
"Error 0x80004003 while preparing to load the package. Invalid pointer . " "Error 0xC0011008 while preparing to load the package. Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored. . "
It is not occuring on the same packages. It varies every time it is run.
I can run every one of the child packages individually, using the same login ID that the parent is executed under.
The parent package works fine on my local machine and other servers running the same version of SSIS. Just not on this server.
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));
