Share Variable With Child
Feb 22, 2007How a parent package can share its variables with child when calling it using Execute Package Task ?
View 5 Replies
ADVERTISEMENT
Parent-Child Variable Issue
Apr 23, 2008Hello,
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.
What did I miss ?
Thanks in advance for your help.
Regards
Using Same Table Variable In Child Stored Proc
Feb 5, 2008Hi
I wanted to use the table variable in Stored proc , for that i have create the table variable in the main SP which will be used by again called sp(child SPs)
now when i am trying to use the same table variable in the child SP, at the time of compliation it is showing error
Msg 1087, Level 15, State 2, Procedure fwd_price_cons, Line 149
Must declare the table variable "@tmp_get_imu_retn".
Can any body give me the idea how to complile the child SP with the same table variable used in the main SP.
Thanks,
BPG
Variable Access In Master Child Package
Dec 2, 2007
I have a master package which calls two package one after an other.
1) I will be passing parameters to master package using dtexec with /set property to set the variables in the master package. I would like to know how can child package access these variables.
How To Get A Variable Value Return Back From A Child Package ?
Sep 29, 2006I have a parent package (i.e. P) and a child package (C). I was able to easily pass a variable value from P to C. And C did computations and get a result (i.e. integer) and stored this integer to a variable (x).
Now I want the value of "x" to be passed back to P and store in P's variable (say Px). Anyone knows how to ?
Thanks
Steve
Using Variable/Expression To Log With Parent/Child Packages
Aug 17, 2006I 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!!!!
Anybody know how to fix this?!
Parent-child Variable Issues That May Impact Deployment
Nov 14, 2006We 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.
Checking For Existence Of Parent Variable In Child Package
Oct 16, 2007I 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?
Thanks.
SSIS Parent/Child Package Variable Passing
Feb 7, 2006Hi
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.
Any help would be appreciated.
Thanks in advance,
Gordon Radley
Passing A Variable Between A Parent And Child Package Doesn't Work
Apr 7, 2006Has 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.
SSIS Parent/Child Package Variable Handshake Issue
Jun 23, 2006 Hi,
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
SQL Insert In Aspx.vb File (or Share Variable With .aspx Page)
Mar 14, 2008Hi,I have a form page with an insert like this:<asp:SqlDataSource ID="addProductDataSource" runat="server" InsertCommand="INSERT INTO test( title1, firstName1, lastName1, dateOfBirth ) VALUES ( @title1, @firstName1, @lastName1 @dateOfBirth )" ConnectionString="<%$ ConnectionStrings:yourQuoteCentreConnectionString %>"> <InsertParameters> <asp:ControlParameter ControlID="dateOfBirth" Name="dateOfBirth" Type="DateTime" /> <asp:ControlParameter ControlID="title1" Name="title1" PropertyName="Text" /> <asp:ControlParameter ControlID="firstName1" Name="firstName1" PropertyName="Text" /> <asp:ControlParameter ControlID="lastName1" Name="lastName1" PropertyName="Text" /> </InsertParameters></asp:SqlDataSource> In default.aspx.vb I have:Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick 'CREATE DATETIME FROM USERS BIRTH DATE Dim dateOfBirth As New System.DateTime(applicantYear.SelectedValue, applicantMonth.SelectedValue, applicantMonth.SelectedValue, 0, 0, 0) 'INSERT TO DATABASE addProductDataSource.Insert()End SubI would like to reference the variable dateOfBirth created in default.aspx.vb in the insert query on the page, or ideally move the insert query into the aspx.vb page. Would this make more sense to do?Thanks,Paul
View 6 Replies View RelatedHow To: Variable File Names When Exporting A Report To A File Share
Jan 19, 2007I'm exporting reports daily to a file share and I need to rename the reports with a pseudo time stamp.
Example: I have a report named "Disk Usage" and when I export (using a data-driven subscription) I want to rename it "Disk Usage - (Jan07)" - or something to that effect.
Can anybody tell me how to accomplish this.
Variable In A Foreach Loop Becomes NULL When Package Executed As Child Package
Dec 3, 2007I have created a SSIS package with a Foreach Loop including a Data Flow Task, which in turn include a Row Count component which pass the row count value to variable with package scope. The variable is used in an Execute SQL Task following the Data Flow Task.
The package executes successfully when executed on its own, but when executed as a child from a parent package (which only include an Execute Package Task) the variable from the Foreach Loop becomes NULL.
There are a lot of other variables in the package receiving values dynamically without any problem, the row count variable however is the only variable in the package that receives a value as part of a Data Flow (and used in following tasks within the Foreach Loop).
Why does the variable become Null? For your information, I am using a variable with package scope and no variables from the parent package are used or passed from the child package to the parent package.
(For your information, we are running the 64 bit version)
Parent Package Variable Assignment Issue In Child Package.
Dec 26, 2006
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.
Please Help.
SQL 2012 :: Group By Parent With One Child And Multiple Child Information?
Jul 25, 2014Basically i have three Tables
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
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
Child,chid Of Child ,child Of Child Of Child
Oct 22, 2007
............child |parent|.............a1 |a | .............a2 |a |.............a11 |a1 |.............b1 |b |.............b11 |b1 |.............b111 |b1 |.............
Here is my table I want to get all childs of "a" ie {a1,a11,a2}I mean recursivelyie child,chid of child ,child of child of child ........etc up to any extentet the table containHow can i for mulate the select querry?
Transact SQL :: To Get Parent / Child / Grand Child Row On Various Order?
Jun 26, 2015I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
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)
Looking for below output ...
Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
Nov 1, 2007I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
Transact SQL :: Parent / Child Tables - Pivot Child Data To Parent Row
May 19, 2015Given 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:
1 2015-01-01Â 2015-12-31 AAÂ 2015-01-01Â 2015-03-31Â BBÂ 2016-01-01Â 2016-03-31Â CCÂ 2017-01-01Â 2017-03-31Â DDÂ 2017-01-01Â 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
[Code] .....
Where Do You Put A Share?
May 24, 2004Environment:
Windows Server 2003.
2 node Cluster single instance of SQL Server 2K sp3a.
1 SAN.
Cluster groups are:
- Cluster group with its own default resources.
- Oracle group with its own resources.
- SQL Server group with its own resources (Server,services,disks,ip address....)
Disk v: is for SQL Server databases
Disk w: is for SQL Server logs
Disk x: is for SQL Server backups.
SO now I have to create a shared folder where the clients need to connect in order to launch the program (executable file *.exe) and maybe tomorrow I will also need to create others shared folders where to put others executable.
Questions:
1)Is it correct to create theses shared folders in the SQL Server group?
2) In wich one of the tree disks?
3) If I have 20 differents programs with differents security will I end up in having a SQL Server group with 20 different resources (file shares)?
Please advise.
Kind regards.
Franco
Pls Share Your Experience With SAN's
Jan 26, 2001Is anyone using sql server 70 or 2000 and a EMC or LSI Metastore SAN? If so, I would like to know how things are going for you. We are currently evaluating both of these products and will be making a decision as to which we will purchase in February. Please advise if you have anything positive or negative to offer.
Thanks.
Gail Wade
gwade@it.rjf.com
Cluster Share
Aug 21, 2000I have setup a Microsoft SQL 7.0 cluster as Actice/Passive configuration on NT 4.0 Enterprise. The problem I am having is that when I create a shared folder on Node B on a shared drive then I reboot Nobe B, the cluster fails over to Node A, but the shared dispeares.
Does anyone have any idea why would this happend.
Thank You,
Piyush Patel
Backup To UNC Or Share?
May 24, 2004Hi All,
Hopefully a painless and quick question to answer:
Am backing up the SQL2000 database(s) using the maintenance plan's under management in the Enterprise Manager.
All is fine however, we are running out of diskspace as the database grows, which is causing the backup to fail if I don't manually remove the nights before.
I have selected to delete old backup files however these files are deleted after the new file is generated and due to the lack of space the new one is not being generated....so in effect the older ones never get a chance to be deleted.
So I was wanting to know if it is at all possible to backup to a UNC path or share . I have tried however it raises an error saying it is not a valid path.
Any assistance here is hugly appreciated (asides from getting new disk - as this isn't a short term option at the moment)...
Cheers all
Troy
File Share
Feb 25, 2007Can someone please tell me what is meant by a file share?I'm doing a little reading on MS Sql Server and they talk about howyou can connect to the sql server through a named pipe.I'm guessing that a file share means a specific service running on aserver. Is this correct?The book says the following:When you connect to a file share you use a Universal Naming Conventionpath to a file server: \FILESERVERSharename.To connect to a named pipe, you connect to a share of the form:\COMPUTERpipesqlqueryThanks Hope you can clear this up for me.
View 2 Replies View RelatedShare Report
Jan 30, 2007I got the following in my report server log:
w3wp!library!1!01/29/2007-17:15:39:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., SharePoint content service is null. Report Server may not have joined the SharePoint farm, or Report Server service account may not have been granted access to farm.;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
I tried to make the report server integrate with share point 2007. all services account are the domain account and local admin, including the applicaiton pool in IIS.
thanks for your help.
ZP
Create A UNC Share
Sep 21, 2007hi all,
i'm trying to write a script just like "copy database wizard" operation, for detaching, copy and ataching a database from a remote sql server to a local server.
Now the problem is when i try to copy the mdf file from the sql remote server to my local server.
Is it possible to create a UNC share in the remote sql server and delete after with t-sql like the Copy Database wizard does?
hope i made my self clear enought.
regards
Backup To Network Share
Nov 27, 2005I would like to backup my databases to a network share (NAS) instead of local disk using Maintenance Plans created by Enterprise Manager. I have successfully used a UNC path to target the destination network share but have not been to figure out how to submit a logon to the network share before the backup is executed.
The SQL Server instance is running in the context of the local system account.
Can I insert a step in the SQL SQL job that is created by the Maintenance Plans that changes the Windows account that the backup runs under? If yes what command syntax would I use in the inserted step or is there another way to accomplish that I'm attempting to do?
thanks
HP
Backup On A Network Share
May 6, 2008Hello,
I am trying to backup a database with a command like:
BACKUP DATABASE my DataBase TO DISK = '\bkSystemkDiskBackup1.bak'
but I get the error 'Cannot open backup device '\bkSystemkDiskBackup1.bak'. Device error or device off-line.
The bkDisk folder is shared, with Everyone full-control access (it's a test environment)
MS SQL 2000.
Backup SQL 2K5 To A Network Share
Apr 5, 2007I attempted to backup my database to a shared drive using the UNC path and it gave me access denied. Any thoughts?
View 2 Replies View RelatedPlease Share Your Valuable Experience
Nov 21, 2007I have SQL Server 2000 database involving voluminous data spread across different locations in a state. The database involves lot of transactions. The database involves tables storing photo images and thumb impressions along with the textual data and other needed information in a single table. I have a application accessing and updating about 5 tables and reading most of the master tables. I need a strategy to design the database such that performance improves. Once the photo and thumb are inserted for a particular party it is not required for retrieval immediately at any circumstance. In this scenario how should I design the database and keep data volume in check. Can I archive photo and thumb impressions in CDs. Will it be reliable and feasible. Please clarify on this issue. Please share your valuable experience. Only insertions involved in the partyinfo table having Photo and Thumb impressions. Later the table is only used to fetch the data of party other than Photo and thumb.
View 3 Replies View RelatedOPENROWSET And Network Share
Dec 15, 2006Hi,I am not able to execute following command from computers other than the SQLserver itself.select * from openrowset(bulk N'\ImageServerModelCam_05.dwg',SINGLE_BLOB) as docI get following error message:Msg 4861, Level 16, State 1, Line 1Cannot bulk load because the file "\ImageServerModelCam_05.dwg " couldnot be opened. Operating system error code 5(Access is denied.).If I use local file path and keep the file on the SQL server then it worksfine from any computer.select * from openrowset(bulk N'D:ModelCam_05.dwg', SINGLE_BLOB) as docThe network share has read access for everyone.What should I do to make it work? I am not allowed to move the files and theapplication which loads them to the SQL server.ThanksSachin G
View 2 Replies View RelatedHow To Share Variables Across Packages?
Sep 6, 2006I'm working on a solution in Visual Studio that has 3 SSIS packages, and now I want to add a 4th that needs access to variables defined in one of the other packages. How can I do that?
View 4 Replies View Related