• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

Error Loading Child Package

i'm trying to execute the 6 child packages , saved on the MSDB of another server, through the  parent  in BIDS of my local machine..I attach the vb.net code i'm using...i get the error message "The specified package could not be loaded from the SQL Server database."...shall i have to save the child packages also on my local machine...or ..need code change...any other solutions...

scripttask.txt
0
ametuer999
Asked:
ametuer999
  • 29
  • 26
1 Solution
 
nmcdermaidCommented:
Looks like you have upgraded from DTS.
You may be better off using a 'Execute Package' task inside a For Loop task. Are you open to doing it this way?
Can you currently run one of these packages in an execute package task if you enter manual values?
0
 
ametuer999Author Commented:
Right now i could do that..but i just  want to make it dynamic .as my the no.of child packages may be more in future..could you please look into my code and check whether all the variables are assigned properly or not..Thanks in advance
0
 
nmcdermaidCommented:
Yes, you can make it dynamic using a execute package task inside a for loop. I understand thats what you're after.
But first we need to establish that you can run a single package from an execute package task and the best way to do this is dumb it down and verify that you can run a single package with fixed parameters.
Alternatively you can try and get your script working, but it is migrated from DTS right? If you have to expend any effort on this then you may as well do it properly (use the Loop task)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nmcdermaidCommented:
Basically you run a For Loop container off whatever you are populating gv_ProcessLog with, and inside that loop you have an execute package task. No code required.
0
 
ametuer999Author Commented:
but the 6 child packages are different and i get the input file gv_filename from gv_processlog for each child package, and it has to select a child package depending on the procedure name populated  from gv_processlog ,how can i achieve this and how can a single execute package task work for 6 different child packs...could you please explain in detailed..avoiding code is really helpful...yeah, its migrated from DTS...
0
 
nmcdermaidCommented:
You put the execute package inside a for loop container. The for loop runs the execute package task as many times as it needs to, with a different target package each time.
its just a matter of setting up the for loop container correctly.
Take a look at this link for some basic examples of how to configure a for loop.
http://www.sqlis.com/post/For-Loop-Container-Samples.aspx
You will need to give me more details on what  gv_ProcessLog is - is it a rowset object, and how is it populated?
 
But your original issue is that it can't run a chld package on a remote server. So you first need to verify that you can use the execute package task to run one of these child packages manually, without getting a 'package couldn't be loaded' error if you can do that then it is worth pursuing this solution.
0
 
ametuer999Author Commented:
gv_ProcessLog is an object data type variable having the fields 'filename','ProcedureName(child package Name)', and 'AppID' .So depending on the filename placed in the folder the parent package has to select the child package to be executed and has to supply the parameters file name and appid to that child package,which i am trying to achieve through my script task....gv_processLog is a result set of an execute sql task ,preceded by the script task...hope i am clear...Thank you ...and how could i watch the value of a object data type variable using break point..its just displaying the value as 'sys.object.'..could you please clarify this also...Thanks again...
0
 
nmcdermaidCommented:
How are the ProcedureName and AppID derived? Are the looked up in a table based on the filename? Its possible you could use a For Each File loop
See here for more info:
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
If you take a look at the picture, Instead of having a 'Import the Files' task you would have an Execute SQL Task to look up the procedure and AppID, followed by a 'Execute Package' Task which is populated from the filename, ProcedureName and AppID
Apologies for not giving you a specific solution right now but I would prefer you understood this a bit, also I would like to collect all the requirements.
0
 
ametuer999Author Commented:
But my doubt is we can configure only one package in execute package task...
0
 
nmcdermaidCommented:
Yes we configure one package in a single execute package task to verify that it can run a remote packge. Then we run that package six times (using a loop), assigning a different package each time.
In the supplied example code, you have only one line with
oPkg.Execute()
This executes a package. But just because it is only one line in your script doesn't mean it gets run only once. It gets run as many times as the outer loop tells it to run.
The execute package task I am talking about is analogous to the oPkg.Execute line, and the For Loop task that I'm talking about covers the For Each row In dt.Rows code (and might also cover the tasks that populate the gv_ProcessLog variable)
Basically I am proposing a solution that requires little or no code. The script task that you are using at the moment is a hangover from DTS and should probably be removed if possible (particularly if the skill set of the people maintaining it covers a visual tool vs a scripting tool)
So please, as a test, in a totally seperate package, verify that you can use an execute package task to run a package on a remote server.
0
 
ametuer999Author Commented:
Yes Sir , I tried it successfully...by execute package task...it will be really very helpful for me avoiding code...i appreciate it ...but can you please explain in more detailed way about the above given for loop and execute sql task  and execute package task in my package... i could understand what you try to explain me...but one example, will really make me learn quickly , in which same execute package task can call and execute different packages in different iterations....
0
 
nmcdermaidCommented:
Here is one example - unzip then rename to from .txt to .dtsx and open and run in BIDS (BI Development Studio)
It simply loops around a task which displays a message.
We have to do the following to it:
1. Change the loop to work off whatever gv_ProcessLog works off
2. Change the script task to an execute package task
So can you tell me how gv_ProcessLog is populated?
I assume that it looks at files in a folder, then goes and looks up some other parameters based on that. The result is one record for each file, plus some more fields per file. Can you expand on what the script task and the execute SQL task contain?
 
If it indeed looks up files in a folder then we use a for each file task instead of a for loop task. Then inside that we have an execute SQL task to look up the parameters followed by the execute package task.
 
If you can clarify this I can send you an example.

ForLoopExample.zip
0
 
ametuer999Author Commented:
Please find the attached package,,,
package.txt
0
 
nmcdermaidCommented:
OK, so gv_ProcessLog is created from a database record set at the Build Loop step. You should be able to replace your 'Loop through applications' step (and probably some other steps) with a Foreach ADO Enumerator, using gv_ProcessLog as its source.
Let me have a play and get back to you.
0
 
ametuer999Author Commented:
Thanks in advance
0
 
nmcdermaidCommented:
Sorry I haven't been able to spend much time on this today. I have a loop working from a recordset (I've attached it but you'll need to change the connection manager and the SQL statement). The loop is just calling a script that shows a messagebox rather than calling a sub package.
Its simple to make it call a different subpackage based on that name, but its a little trickier to pass down the other six global variables. I will do some more investigation tommorow. We will probably use configurations to do what we need.

ForLoopBasedOnRecordset.zip
0
 
ametuer999Author Commented:
Thank you.
0
 
ametuer999Author Commented:
Hello Sir,
i tried your solution.But my question from the beginning is, how can you map a variable in execute package task...i know to give the name of the package from sql server or file system physically...but how to get a package executed using a variable ...could you please explain me in detail with some examples if possible , ASAP please....Thank you once again for your time and efforts so far...and also the bringing down of other variables to child packages...
0
 
nmcdermaidCommented:
It looks like this is the way that you use a parent package variable:
http://www.sqlis.com/post/Using-Parent-Package-Variables-in-Package-Configurations.aspx
I will complete the sample package for you shortly.
0
 
nmcdermaidCommented:
OK, here you go - unzip and rename to .dtsx
The ForLoopBasedOnRecordset package reads data from a table in the populate recordset task
The for each record task loops for each record found. For each loop it runs the tasks inside - a single Execute package task.
 

CallChildPackagesUsingRecordset.zip
MasterPackageExpression.jpg
0
 
nmcdermaidCommented:
oops posted too early... explanation continues here...
The screenshot above shows that everytime the 'execute package' task runs, it uses the value of the gv_Package variable for the child package name.
Thats the master calling child part covered (at a high level)
Now, open one of the child packages, go to SSIS / Package configurations, you'll see the screenshot below. That is saying 'get the value of these two variables out of whoever called me'.
So its backwards to the DTS method - the master package populates its global variables, but they aren't pushed into the child package, Instead the child package sucks them out of the parent.
That is it at a high level. Can you have a try and ask questions and I will be able to ansewr specific questions.
Oh also, all the child packages do is display the values of their two variables.

ChildPackage.jpg
0
 
ametuer999Author Commented:
First of all, extremely sorry for the late reply..i was out of my place in the weekend..
IT'S AWESOME.. almost reahed the goal...only thing missing is only childpakage 1 is being executed for all files , i doubt the connection manager used in connection of execute package task editor..is it to be managed dyanmic...or any other suggesion...though the package name is mapped to variable...how could the execute package task get the path of the child package to be executed..?
could you plese look into this...? and could you please let me know how to give marks more than 500 in our Experts Exchange?
0
 
nmcdermaidCommented:
Thats OK, sorry for taking so long to answer. No need to assign more than 500 points (its not allowed anyway!). In actual fact I have also learnt something and that is my primary goal on this site.
Anyway, in your master package, ensure you have done the following (I did not explain very well above):
1. Double click the execute package task and press 'Expressions' on the left hand side (below General and Package)
2. Now click 'Expressions' on the right hand side, then click the [...] button to open the Property Expressions Editor. In here, select 'Package Name' under property.
3. Now click the [...] button under the Expression column. This opens the expression builder. On the left hand side, find the variable that holds your child package name (in my case, gv_Package), and drag it to the bottom. Press OK. It should now look like the 'Property Expressions Editor' screenshot
4. Press OK to close the property expressions editor. Press OK to close the Execute Package Task Editor
What you have just done is told the package to use the contents of your variable as the destination package for this task. You can do this kind of thing all through SSIS, it's very handy. Every task has an 'Expressions' area that lets you substitute a variable (or an expression) for a property value.
Try that and get back to me.

Expressions1.jpg
Expressions2.jpg
ExpressionBuilder.jpg
PropertyExpressionEditor.jpg
0
 
ametuer999Author Commented:
I already did it sir..but only child package 1 is being executed..that's my worry...remaining  5 packages are not executed..my doubt is in execute package task editor in connection i gave the path of the child package saved, should i use any expression there also for 'connection'...and though the remaining packages are not loading the files in staging tables the final output is good...my child packages load different structured files to different staging tables...could you please look into this finally sir..
0
 
nmcdermaidCommented:
You're right... tis not dynamically assigning the target package. Its meant to. Let me look into this further.
0
 
nmcdermaidCommented:
I think you're right - it needs to be set in the connection manager. This may require code after all (but a lot less!)
0
 
nmcdermaidCommented:
Or maybe you need to create three connection managers pointing at three packages, then update the connection manager name. Seems silly to me but those connection managers have always seemed unwieldy to me. When I get a chance I will try it out.
0
 
ametuer999Author Commented:
Thank you ,
0
 
nmcdermaidCommented:
I'm fairly sure you'll need to create three connection mamagers and use an expression to change which connection manager is being referenced by the execute package task.
I may not get a chance to do this today sorry but I will do it as soon as I can.
0
 
ametuer999Author Commented:
no worrys, you can do whenever you have time but i have 6 child packages, so i think i need 6 connection managers , thanks once again..
0
 
nmcdermaidCommented:
Yes that definitely works.
Create six file connection managers to each of your child packages. The name of the connection manager must match the procedure name in your table.
Now, in your execute package step, in expressiosn, set the Connection property to the gv_Package variable.
I have a sample package that shows you but you need to alter it to see how it works.
Can you have a try at it and let me know if you have any questions.

ForLoopBasedOnRecordset.zip
0
 
ametuer999Author Commented:
oops, its not accepting, gv_Package has only the package name , not the path..i doubt it cant take mere package name as the total path..do we need to create new variables to hold the path, i tried that,i saved the package path in gv_packagepath and in connection i gave as gv_ packagepath+gv_package ,not working...i guess we have to try some other way...thanks..
0
 
nmcdermaidCommented:
OK, it works like this:
1. The connection manager contains the path to the package (create one connection manager for each package)
2. Each connection manager has a name
3. This is the name that you need to set in the Connection property in the execute package task
In the screenshot below you can see four connection managers. Three of them are my file connection managers to the packages. They are called ChildPackage1, ChildPackage2, ChildPackage3
Also in the screenshot below you can see that the 'Connection String' property is the path to the package. Note that the connection manager name (ChildPackage1) and the actual physical package name (ChildPackage1.DTSX) do NOT need to be the same. I could call the connection manager 'bugalugs'
Now if I DID call the connection manager 'bugalugs' then that is what needs to be in gv_Package, before it is assigned to the Connection property of the execute package task.
Does that make sense? gv_Package is the name of the connection manager, not the name of the actual physical package.

 

ConnectionManagerToPackage.jpg
0
 
ametuer999Author Commented:
'Now if I DID call the connection manager 'bugalugs' then that is what needs to be in gv_Package, before it is assigned to the Connection property of the execute package task.'
Sorry , i can not follow you, i have a couple of doubts again from the above statement..yes, my connection manager is also same as of yours..but calling the connection manager is the problem ...if you asssign one connection manager in the connection property of execute package task..ITS GOING TO BE STATIC..HOW TO MAKE IT DYNAMIC..HOW TO CHANGE IT FROM ONE CONNECTION MANAGER TO ANOTHER DEPENDING ON THE FILE INPUT..and gv-packagename is the variable extracted from for each loop from gv-processlog, in the query it returns only package name , path not included..i've trying all different ways i could..but not able to execute remaining child packages except the one i gave in connection property ...please bare with me..and could you please clarify..i guess only script task can solve my problem...thanks..thanks..

0
 
nmcdermaidCommented:
HOW TO CHANGE IT FROM ONE CONNECTION MANAGER TO ANOTHER DEPENDING ON THE FILE INPUT
Use expressions as described in comment ID 24300044 above. In my prior comment I assumed you put the physical package name in here. But you don't, you put the connection manager name in here.
Expressions are a way of dynamically assigning a variable value to a property of a an SSIS object. In this case the package connection manager property of the execute package task.
The sample package I posted does in fact call different packages so you definitely don't need to go the script option.
0
 
ametuer999Author Commented:
Now i changed my query to get the package name along with the path into gv_package,,and i been trying to do the same as you showed in your screen shots, but when i map gv_package variable to connection in the expression builder and try to save i get the message as ahown in the screen shot attached...sorry, i would have sent it to you much early..and if i map the gv-package to packagename in the expression it gives no problem...could you please look into this once...sorry for all the trouble i am giving you for last few days...thanks again
untitled.bmp
0
 
nmcdermaidCommented:
You probably need to go into your gv_PackageName variable and set it to a valid connection manager name. It's trying to immediately apply the expression to the property at design time, but the variable is blank at design time.
Don't apologise for asking questions - we're both learning and thats a good thing.
0
 
ametuer999Author Commented:
i opted delay validation true in the properties of  execute package task..but no use..could you please explain how to implement the above said solution...

sweat..thank you sir...
0
 
nmcdermaidCommented:
Yes, I would have thought delay validation would have fixed it also...
Try this:
1. Click in a balnk area on the design surface
2. Press SSIS/Variables to open up the Variables screen
3. Go to your gv_PackageName  variable and enter one of the package connection manager names
 
It may have thoroughly confused the execute package task already though. If the above still doesn't work, delete your execute package task and recreate it (ensuring the variable is populated, and the expression is set up)
If that works, then set delay validation = true, and clear out the variable and see if that also works.
0
 
ametuer999Author Commented:
nope, throwing the same error..can we try using 6 different execute package tasks in FEL or any other solution..
0
 
nmcdermaidCommented:
Well you certainly could use six different execute package tasks but they would all be hardcoded and there would be no point looking them up in the database.
I can tell you that the last package I posted does exactly what you want: It executes three packages based off three package names held in three rows in the database. I did have similar problems to you but I managed to sort them out.
So try this:
1. Delete your execute package task
2. Ensure your gv_PackageName variable has a valid connection manager name in it
3. Create a new execute package task, hard coded with the connection manager you set at 2. Save the package and verify no errors.
4. Assign the expression as described way up above. Save the package.
If you get errors please post your package again and I'll take a look.
0
 
ametuer999Author Commented:
Thank you..i will try again and let you know ASAP
0
 
nmcdermaidCommented:
OK, good luck.
0
 
ametuer999Author Commented:
no sir, still same problem..i know i am doing some mistake..i attach the package here..could you please look into this..
mypackage.txt
0
 
nmcdermaidCommented:
The problem is that you have the package path in your gv_PackageName.
You need to put the connection manager name.
Change gv_PackageName to be Import_Profile_Generic
0
 
nmcdermaidCommented:
Also I think the path is messed up in the connection mamager - you have Integration Services Project22 instead of Integration Services Project2 (note the extra 2)
0
 
ametuer999Author Commented:
Actually morning before sending you, i was trying to just save the child packages in another project from parent i.e.22, yup its right..i tried even with same package as parent..yeah,the path is messed up ,.rename them as you like...Thank you..
0
 
ametuer999Author Commented:
Oh sorry, i did not see your first post...in the beginning it was like that only ..i.e. the package name..i was failed even that way...then i tried this...
0
 
nmcdermaidCommented:
No not the package name, the connection manager name
The package name is Import_Profile_Generic.dtsx
The connection manage name is Import_Profile_Generic - note that it does not have the .dtsx extension.
 
Can you change your variable value to Import_Profile_Generic  then screenshot the error if you are getting one.
 
When I opened your pacakage, the error indicated that it could not find a connection manager called C:\SSIS\...\...Import_Profile_Generic.dtsx' which made sense because there was no connection manager called that. There was one called Import_Profile_Generic though (see them listed along the bottom of your screenshot at post 24331980). Thats what you have to set it to.
0
 
ametuer999Author Commented:
i changed my variable name as Import_Profile_Generic. and the same error repeates...i saved   'C:\SSIS\Integration Services Project2\Integration Services Project2\Import_Profile_Generic.dtsx'  in my Import_Profile_generic connection manager..and  in execute  package task in the expression editor i am trying to map connection with the variable gv_PackageName, which does not have .dtsx..yep, you are right,,i am confused in one thing..have to give the connection manager name or the full childpackage path that is saved in connection manager to be given in connection property of execute package task...
0
 
ametuer999Author Commented:
I got it Sir...post the deatails later...Thank you somuch...
0
 
nmcdermaidCommented:
No worries, I'm glad we cracked it.
0
 
ametuer999Author Commented:
i have to give the connection manager name only like'Import_Profile_Generic' for my variable , save it and map it in execute package task...anyhow thank you somuch for all your patience all these days..i am going to close this ..but how to contact you , if at all i have some other doubts...please let me know and i will finish this...THANKS...THANKS AGAIN....
0
 
nmcdermaidCommented:
Glad you got it sorted. What I've been saying since comment 24310144 is to use the connection manager name. Sorry I misled you at the start.
For future help, if you post something in the SSIS area, me or one of the othe experts can help. It looks like you have a big project goin on there.
I will add you to my watchlist so I am aware when you post another question.
Please note if you post a new question, its generally not polite to ask for an Expert by name - everyone here is quite capable of helping. It DOES help though if you post a link to a prior question (such as this one) so that experts can get some backgrounf on the question.
Good Luck!
 
0
 
ametuer999Author Commented:
THANK YOU SOMUCH
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 29
  • 26
Tackle projects and never again get stuck behind a technical roadblock.
Join Now