I have a child package which is executed several times within the same SSIS ETL. I have placed a break point on one of the child package's tasks, set to trigger on a PreExecute() event. The first time the child package is invoked, the breakpoint is triggered. However, on each successive invocation the breakpoint is ignored. Does anybody know if this behaviour is normal? Thanks in advance!
View 1 Repliesi have a package which logs to a table the status of its execution. i wonder if there is a way to trigger an event when package execution is terminated.
Hi,
I created a package which passes some infornmations( through parameters) to its child package.
I need to do some processing in parent package based on execution status of child package.i.e.
if child fails then some operation and if child succeeds then other operation.
To determine the status of execution of child package I am using two differnt constraint ..one constraint is having value "Success" and other having value "Failure".
My problem is that when child packge is executed successfully the constraint with value = "Success" works properly but when child fails the constraint with value "Failure" does not work.
-Prashant
I have a parent package "A", I also have 4 child packages "B","B1","B2",B3"
In BIDS, I created a file connection in the package "A" to connect to the child packages. So whenever I want to run B1 or B2, I change the path in the file connection to point to B1 or B2 and so on. Since the developement environment is File system this works perfectly fine.
But in the Test / Production environment all packages are stored in the Sql server. How can I paramaterize the child package connection so that I can use configuration / variables to select which child package to execute.
Thanks
I ran into a variety of problems trying to set a script task breakpoint in a package containing multiple script tasks. The debugger apparently treats the breakpoint as if it were set in ALL tasks in the package, not just the one in which it is actually set.
At best this results in hitting breakpoints in scripts where they are not set and at worst the debugger brings up the "Send error report" dialog and quits (while the package continues to run). The latter seems to happen most often when the script with the breakpoint has more lines than an earlier script and the breakpoint is set at a line number that exceeds the number of lines in the earlier script--it bombs when the earlier, shorter script starts.
To get the debugger to work under these circumstances I had to add some nonsense code like
While False
Dim i as Integer = 0
End While
to every script, at the same line number near the beginning of the script (line 40, for example). I then set a breakpoint on the middle statement in one of the scripts (doesn't matter which) to cause the debugger to open at runtime. It doesn't hit the breakpoint because the line is never executed. If the breakpoint is set on a line that can be executed in any script in the package, bad things tend to happen.
I then add a "stop" statement to the script that I want to debug. This only works if the debugger is already open, hence the dummy breakpoint above.
This workaround is usable, but I am debugging a package that has quite a few scripts and having to insert dummy code in all of them at a fixed line number is rather inconvenient. I would really like to see breakpoints work the way one would expect--only in the scripts where they are set.
Is there some other, easier way around this problem? Is there at least an easier way to get the debugger to open so that "stop" will work?
Hello all. I have a stored procedure that seems to skip the execution of an entire line of code (which happens to be a call to execute another stored procedure), but ONLY if it is being used from C# (using the System.Data.SqlClient namespace... object: SqlCommand, etc).
Basic structure:
Event SP: top-most parent
-->Calls UpdateXs SP: This is the SP that will not call his child SP
----->Calls UpdateX: This is the SP that is being ignored when called from .NET (In a loop, each iteration, max iterations 10).
(1) I can call the top-most stored procedure (Event SP) with the same arguments in Query Analyzer and all SP's will execute.
(2) I can call UpdateXs SP with the same arguments that are being passed to it from Event SP in the Query Analyzer, and again, it's child sp executes (UpdateX).
(3) I can also call the last child (UpdateX) directly in Query Analyzer with the same arguments that are passed in production (from executing the top-most parent in C#), and it executes correctly.
I'm positive that at no point the last child (UpdateX) is being passed NULL values. I made debug tables and before execution of an SP from within another SP, I store all the arguments in a debug table--none are null. Also, to figure out what was wrong with the last child (UpdateX) , the first statement in inserts into another debug table, just to show that that execution has made it into the sp--when the top parent is called from C#, it doesn't insert into my table (doesn't execute).
There are statements inside Event SP that execute after the sp call to UpdateXs SP. These statement always execute. Also, there are statements inside UpdateXs SP that execute after the sp call to the last child. These statement always execute too.
I am now clueless. I had developed & debugged all sp's in Query Analyzer before trying to use it live (with the C#). It's always fine in Query Analyzer, but ALWAYS skips the last sp in production.
Is there some fundamental principle I am missing here in MS SQL stored procedures? I am only 3 levels deep in SP calls, so I didn't think that would be an issue (I have made SP's that went a LOT deeper in calls than that in the past with no issues).
Any insight much appreciated. Thanks in advance.
I 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)
Hi there
We have a SSIS run which runs as follows
The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1:
The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €œcannot connect to database xxx€?
Info - €œpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €œtimeout when connect to database xxx€?
Info - €œpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
Thanx,
/Nils M - Copenhagen
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.
hi,
I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis.
I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent.
I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent...
Any suggestions?
Thanks for any help in advance..
smathew
I use a execute package task to run a child package in which I run some sql task.
as the error handle I insert a script task and link a line from execute package task to script task
of course the line is red,
but I found when the child package failed, the execute package task turns red,it stopped
the script task can't be run, I don't konw why?
Hi guys,
Here's the deal. I have a child package, (say, pack01.dtsx), which uses a dtsconfig file for its connection string, which can be called from other packages, but which also can be called by itself.
However I also have another package (say, pack02.dtsx) which uses the same dtsconfig file for its connection string. It calls on pack01.dtsx.
When I use DTEXECUI and run pack01.dtsx, specifying the proper .dtsconfig file, it goes well. But when I try and run pack02.dtsx, an error occurs saying pack01.dtsx connection cannot be established.
How do I pass the connectionstring being used by pack02 to pack01, without having to remove the configuration file setting of pack01? Can a Parent Package configuration and a configuration file try and map to the same property?
Hope someone could help. Thanks.
Hello All,
I have a SSIS Package with a "Execute Package Task" to call a child package. I am trying to have the master/parent package complete its execution regardless the outcome (failure or success) of the child package. The overall structure of the master package is:
1. Perform Pre-load tasks (stored procedure).
2. Execute Package Task (call child package)
3. Perform Post-load Tasks (stored procedure)
I have try everything and cannot get the results that I want... I have tried the combination of "failparentonfailure", "forceexecutionvalue", etc. The master package stops at childs failure. I would like to resume to completion
Any ideas out there?
Rohan
I have two SSIS packages in a project, one calling the other. The parent package works fine in my local mechine. After they are deployed to the production, I schedeul jobs to run the packages in the SqlServer. The child package works fine if I run it alone, but the parent package could not find its child package if I run the parent package . As I checked, all xml config files and the connection string pointing to the child package were set correctly. It seems the parent package did not use the xml config file. Can someone help me? Thanks in advance.
View 9 Replies View Related Hi,
In my application, i have two package, parent package and child package. the parent package is executing child package using a Execute Package Task.
"Execute Out Of Process" property of Execute Package Task is set to TRUE. means the child package will be run in separate process not in the process of Parent package.
this was working fine, but at a particular client location. its failing the error is "not able to load child package".
for me it seems some setting on server restricting to create separate process for child package execution.
when "Execute Out Of Process" property of Execute Package Task is set to FALSE. its working fine.
can anyone help what could cause its failure with property set to TRUE.
Appreciate any help.
I am trying to figure out how to run an SSIS package that is encrypted but also has a child package which is encrypted. I am trying to run from the cmd line using dtexec and specifying DECRYPT <pwd> for the parent package but how do you specify for the child package.
Please help if you have seen such an issue before or you have a work around.
Thanks
Newbie
I have a parent package which calls a number of child packages. Occasionally, I have one child package which starts up and appears to finish but the parent package never gets notification that the child has finished. If I view this in Management Studio's in Integration Services, I can view the packages that are running and only the parent package is running. Because it never gets control back, the next child package will never get started and the parent package will continue to run and never finish.
Any idea what could be happening??
I need to pass variables from the parent package to the child package. I got this to work in a small test case. But in the actual project, I get an error when I try to do that. The parent package's log has this error :
Error: Error 0xC0012050 whike loading package file "c:.......". Package failed validation from the ExecutePackage task. The package cannot run..
There's nothing more to go on here in the log.
Any ideas ?
-chiraj
I run into some issues and really need some expert help here.
Here is the problem. I have two packages (parent.dtsx and child.dtsx). Both package have its own configuration file (parent.dtsConfig and child.dtsConfig). The file Child.dtsConfig contains a variable (i.e. "X") that is to be used by Child.dtsx.
Inside parent.dtsx. there is a package-task that calls into Child.dtsx. It worked perfectly well if I run parent.dtsx using Dtexec or from inside SSIS's IDE.
Now I want to programmably call "parent.dtsx" from my C# code. I loaded package using "app.LoadPackage"... Inside C# code, I want to reconfigure Child-package's variable ("X"). I then loaded in "Child.dtsx". However when I run "parent.dtsx" and child.dtsx still loads the original value for "X". The reconfigured value for "X" is not updated.
Please help on how to get around this issue.
Thanks.
Is it possible to call a package through the 'Execute package task' and force it to run in 32 bit while the master package runs in 64 bit?
View 1 Replies View RelatedHi there
I have a parent package which execute a child package. But when the child package is run from the parent not all of the tools inside is executed. I have a variable "ExecuteStep" from the parent that gives information about which tools, that needs to be executed.
BUT
The child package should be able to run independently also. In this case all tools inside the package should execute.
This is however not possible because my variable "ExecuteStep" does not exist inside the package, it is inheritage from my parent.
If I create a variable with exactly the same name inside my child package, I can run the child independently, but when executed from the parent the value from the parent package is now overwritten by the child variable.
How can I get around this problem?
bjarne
Hi all,
I am a little bit confused about Transactions, the connection string parameter "RetainSameConnection" and the execution of a child package...
I have a parent package, which should execute some child packages in a loop depending on a sql dataset. doing this using variables works fine. I am setting the variables for Source and Destination also and I am having a connection manager for source and destination in the parent package.
The child packages alos have source and destination connection managers depending on variables coming from the parent package.
I am forced to use transactions on the destination, to be able to rollback if an error occurs.
When I initiate the transaction in my parent, call the child package with its own connection manager depending on the variables coming from the parent package will it be the same Transaction in my child and will it be possible to commit the transaction?
If this is not a proper way to do it is there another way to handle this?
Any help appriciated!
Thanks in advice"
cheers
Markus
I had a child package AAA
The ProtectLevel of AAA is "EncryptAllWithPassword" and PackagePassword is "passtest"
now I put a Execute Package Task on Control Flow TAB of Package BBB
in this task I want to Execute package AAA,after set Password "passtest" of Execute Package Task ,I execute package in command line
DTExec.exe /F BBB.dtsx
I get error message,
can't decrypt package AAA
Where can I set the PackagePassword of AAA in Execute Package Task.
I have a parent package that calls a child package. When this is executed on SQL server (the actual server host SSIS services) it executes fine I can see data in the correct tables and I can determine that is ran. But I try to run this from my local pc within the designer (Visual Studios) and the parent package runs a few task that are before the child package task but once it gets to the child package it stops. I have set breakpoint on several task with the parent package and I can step thru them but once it gets to the child it tries to run it then the execute package task object turns red. I can not find any error messages or codes. I even have beak points set up on the child package itself and the process never hits those break points but I'm assuming is cause is puking before I get to the child package.
The child package is stored in SQL server and is in MSDB.
I just want to debug the child packages from BIDS or visual studios?
any one know how to debug child packages from BIDS or Visual Studios?
I can if I run the child package by itself but I need to test from end to end ....so within the parent packages which calls a few child packages..
thanks
Hello,
I have a package which runs several child packages. All works well and everything runs, but when it runs each of the children packages, it opens it, runs it and then it stays open. When the whole thing is done, there are about 25 or so open packages. Should they close after they run? Is there a setting I need to do this?
The point I am in SSIS is that I have gotten a decent feel for creating packages, but everything is still in debug mode. I need to take the next step to learn how to have this stuff run automatically or from a procedure outside the SSIS interface. Does that make any sense? If so, where can I learn about that.
Thanks for the help.
-Gumbatman
Hi there,
sorry if the message seems a bit garbled i cannot see the textbox properly with ie7/ff
anyways. the situation.
childpackage contains a loop-container. in there i use an ole-db-source with a variable
based on an expression. ie. "select * from foo where bar=" + len(@[bar]) == 0 ? "initial" : @[bar] + " and etc=1"
where bar is the variable that is set by the loop.
this works great.
however i need to call this package several times, only the expression is a tad different.
so i need a scripttask that sets the expression correctly, then i call the childpackage
and map the current-expression to the expression in childpackage.
how do i do that? or am i doing it wrong?
my script-task looks something like:
dts.Variable("theVar").Expression = " ""select * from foo where bar="" + len(@[bar]) == 0 ? ""initial"" : @[bar] + "" and etc=1"" "
in the childpackage i have a package-conf that maps thevar to thequery with target-object expression.
Hi All,
May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help
Thanks
Hopefully a simple question about parent-child package relationship. For this example, let's say I have a simple setup - one parent package: parent.dtsx, and one child package: child.dtsx. The parent package calls the child package via the ExecutePackage Task.
If I add an OleDB ConnectionManager to the parent package called MySqlConnectionManager, should I be able to reference this connection via a script task (or custom component) from my child package? I realize that I will have a problem doing this at design time, but I thought I could get around it with the script task or custom component. That said, when I look in the Connections collection at run-time from within my child package, I do not see the parent package's MySqlConnectionManager. Am I missing something, or is this the way it was intended to work?
Thanks,
David
I had another thread discussing the intricacies of FailParentOnFailure, MaxErrorCount, and error event propagation. Check it out here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3093944&SiteID=1.
In doing further research I think I may have come across what seems to be a bug, but perhaps there is some reasoning behind it. Take the following package structure:
Package1 --> Task Execute Package2
Package2 --> TaskA
TaskA is a task that I have constructed intentionally to generate errors. When executing Package2 on its own separate from Package1, TaskA errors and the package completes with a result of Failure. This is what is expected.
Now, add an OnError Event Handler to TaskA, which does nothing, and set its Propagate variable to True (the default). Again executing Package2 on its own causes the package to complete with a result of Failure. Again, as expected.
Now, for the OnError Event Handler of TaskA, set the Propagate variable value to False. When executing Package2, we now get a result of Success. Again, this is as expected since TaskA does not have FailPackageOnFailure set to True.
One would think that if Package1 is now executed, since TaskA in Package2 is preventing the error from propagating up, that Package1 would report success, right? Wrong.... at least according to my tests. When Package1 executes, the "Task Execute Package2" lights up red and Package1 finishes with a result of Failure. In order to get around this, I have created an OnError Event Handler on "Task Execute Package2" which has its Propagate value set to False.
Does anyone know if this behavior is by design or by accident??... or is it user error?
I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.)
I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start.
Could somebody explain why this is happening? any suggestions/ similar experiences?
Regards
Saurabh
Hi,
I'm having Parent package, which has For each loop which returns Val1 , Val2 , Val3 and Val 4
I want to create child package which accepts one parameter and call this package inside For each loop.
Inside loop i want to call 4 child packages(same pakage) simultaneously.
If i create child package which access parent package variable, i will have to modify modify each child package. I cannot reused child package.
How can i create a generic child package ?
Thanks,
Sandeep.
Have a parent package that calls many child packages (over 30) for a daily data warehouse update. On any given day, it randomly fails to call a child package with the following error:
Error 0x800706BE while preparing to load the package. The remote procedure call failed.
Its not failing the same package each day. I need a better explanation of this error message.
Environment: SQL2005 Enterprise Dec2005 RTM on Itanium64 with Windows DataCenter.
I have this block of code that is in a child package (see below).
The idea is, I want to be able to run the child package by calling it from the parent package AND sometimes I want to be able to run the child package on its own, in which case it will not have access to the variables in the parent package, i.e. "TEST". That is, the "TEST" variable will not be "seen" by the child package because the TEST variable lives at the parent level, not the child level.
In the case of running the child package on its own, it won't find the TEST variable, and it should fail.
However, I do not want the script task to fail as I have a try catch block but it fails anyway even with the exit try. What am I missing please?
Thank you.
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(€œTEST€?) 'this is a variable in the parent package
Dts.VariableDispenser.GetVariables(vars)
Try
vars(€œTEST€?).Value = €œI€™m Done€?
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
€˜ what can I put here so that the script task does not fail?
Exit try
€˜Throw ex - do not want this
End Try