February 23, 2010 at 6:21 am
wschampheleer (2/22/2010)
Griffster (2/22/2010)
So that would mean that the only way I could run the child package independently would be if I created another parent package that only calls the child package on its own i.e. no other tasks. Correct?My suggestion would be to create (or keep) your child package so that it runs independently (i.e. with its own configurations). If and only if any of these configurations need to be different when the package is called from a parent package, then you would apply parent package variables.
My child package is run independently once each every day according to two different configuration files. Would it be easier to copy my child package and leave the original to be setup to run independently using it's own package configuration file, and set the copied version to run via a parent package variable configuration file?
February 23, 2010 at 7:55 am
I'm still guessing what you're trying to do, but here's another suggestion.
If the package is the same, but only the configuration is different, here is what you can do: in the child package, always refer to the same physical configuration file eg. config.xml. Make two configuration files, one for each configuration eg. config1.xml and config2.xml. Just before you execute the child package either copy config1.xml or config2.xml over config.xml. (if you are using configurations in the database rather than config files, you can do something similar with an update statement). These actions can be integrated in the parent package, in your scheduler (eg. sql agent) or executed manually. An alternative to copying files is setting the name of the configuration file in an environment variable and change that.
If you store your configurations in variables before applying them as expressions (this is a best practice), you could 'hardcode' all but one set of configurations into the package by using a script and then use a package parent variable to decide whether the configuration file is to be used or one of the hardoced sets.
February 23, 2010 at 8:36 am
Sorry Willem, I'm not made myself clear. Think I ought to show my steps...
1) I created a package, call it X.dtsx for example and set up an xml configuration file (y.dtsconfig) with about 20 variable values in it
2) I deployed my completed package on to the server and set up two SQL agent jobs to run X.dtsx(saj1 (which is set to run overnight) & saj2 (which is set to run in the daytime))
3) I created two new config files as well to contain the variable I want set on each run, a.dtsconfig and b.dtsconfig
4) In saj1 configurations tab, I placed a.dtsconfig, and in saj2 I placed b.dtsconfig
Both these have been running fine for about a year now.
I now want to write a new package (the parent, Z.dtsx)to do a number of tasks, one of which is to run X.dtsx. I will need it to run X.dtsx (the child) with the 20 variable values from a.dtsconfig or b.dtsconfig dependent on when I'm setting Z.dtsx to run via two new sql agent jobs (saj3 & saj4). However I want the saj1 and saj2 to continue running independently.
As X.dtsx is very stable and has been running fine on its own pulling the variable values from a.dtsconfig and b.dtsconfig I thought it would be easy to incorporate it in to Z.dtsx by an execute package task...not so it seems!!!
I guess a cheat would be add an execute sql task to run saj1 or saj2 but I don't think I should have to do it this way really.
February 23, 2010 at 9:16 am
OK, I think I see where you are heading 🙂
You can dynamically change the connection used by the Execute Package Task using a variable in an expression (the exact method depends on where your packages are stored: in msdb or on the filesystem). You can set this variable in different ways (at runtime using dtexec, using config files, hardcoded in the package or using a scripts that sets the variable based on the time of day). The Execute Package Task would then run either saj1 or saj2, based on the value of the variable. Both packages will also continue to run independently.
If you want to merge saj1 and saj2 into one package, you should use one of the methods I described earlier to switch the configuration in the parent package before calling the Execute Package Task.
February 23, 2010 at 9:28 am
wschampheleer (2/23/2010)
OK, I think I see where you are heading 🙂You can dynamically change the connection used by the Execute Package Task using a variable in an expression (the exact method depends on where your packages are stored: in msdb or on the filesystem). You can set this variable in different ways (at runtime using dtexec, using config files, hardcoded in the package or using a scripts that sets the variable based on the time of day). The Execute Package Task would then run either saj1 or saj2, based on the value of the variable. Both packages will also continue to run independently.
If you want to merge saj1 and saj2 into one package, you should use one of the methods I described earlier to switch the configuration in the parent package before calling the Execute Package Task.
You're almost there! saj1 and saj2 are two SQL Agent jobs (not packages). They both run the same package though, X.dtsx, but run from two different config files a.dtsconfig and b.dtsconfig (set up in the configurations tab within the set up of the sql agent job) which have different settings due to the time of day they're running. The basis of this is that X.dtsx is a warehousing SSIS package. One particular table takes a very long time to transfer but is suffficient to be transferred once a day whereas other tables need to be updated during the day as well. So saj1 running x.dtsx with config a.dtsconfig excludes the big table and is set to run in the daytime, whereas saj2 runs x.dtsx with config b.dtsconfig overnight.
So, I don't want to change the package connection according to the time of day, it's the configuration file run by that child package, x.dtsx.
February 23, 2010 at 10:47 am
You're almost there too 😀
Copy either a.dtsconfig or b.dtsconfig to c.dtsconfig.
Make one minor change to x.dtsx to use the configuration in c.dtsconfig.
Running the package from saj1 or saj2 will continue to work as before.
In your parent package, add a task that copies either a.dtsconfig or b.dtsconfig to c.dtsconfig based on the time of day before running the Execute Package Task. Done.
February 24, 2010 at 6:32 am
wschampheleer (2/23/2010)
You're almost there too 😀Copy either a.dtsconfig or b.dtsconfig to c.dtsconfig.
Make one minor change to x.dtsx to use the configuration in c.dtsconfig.
Running the package from saj1 or saj2 will continue to work as before.
In your parent package, add a task that copies either a.dtsconfig or b.dtsconfig to c.dtsconfig based on the time of day before running the Execute Package Task. Done.
Does this then mean I don't have to use parent package variable configurations in the child? Interesting, so the child is calling c.dtsconfig. saj1 will override this with a.dtsconfig at run time, and similarly with saj2 using b.dtsconfig. The parent (z.dtsx) then overwrites c.dtsconfig with either a or b.dtsconfig using a File System Task. Correct?
Unfortunately the daytime run has to be set off manually as the time of day can vary. I leave this to my support team to run. They don't have SQL on their pc so I wrote a bat file which is run from a Windows Scheduler job on the server. To get round this though, I suppose I could add a variable which would correspond to the dtsconfig I'm copying (either a or b), and another for the dtsconfig I'm overwriting (c) and then after I deploy Z.dtsx set up two sql agent jobs to do the daytime or night time run. Sound good?
Incidentally, I tried the Execute Sql Server Agent Job Task last night to see if I could get that to work and just run saj1 or saj2. It ran the agent job, but the downside being ssis is unable to tell when the agent job has completed which is no good to me as I need to run some other steps after the job has completed.
February 24, 2010 at 7:57 am
Does this then mean I don't have to use parent package variable configurations in the child? Interesting, so the child is calling c.dtsconfig. saj1 will override this with a.dtsconfig at run time, and similarly with saj2 using b.dtsconfig. The parent (z.dtsx) then overwrites c.dtsconfig with either a or b.dtsconfig using a File System Task. Correct?
Correct!
Unfortunately the daytime run has to be set off manually as the time of day can vary. I leave this to my support team to run. They don't have SQL on their pc so I wrote a bat file which is run from a Windows Scheduler job on the server. To get round this though, I suppose I could add a variable which would correspond to the dtsconfig I'm copying (either a or b), and another for the dtsconfig I'm overwriting (c) and then after I deploy Z.dtsx set up two sql agent jobs to do the daytime or night time run. Sound good?
Sure, but you can even leave the copying of the config file out of the parent package and do it in you sql agent jobs (no need to pass variables etc.). Have done it before and works perfectly.
Incidentally, I tried the Execute Sql Server Agent Job Task last night to see if I could get that to work and just run saj1 or saj2. It ran the agent job, but the downside being ssis is unable to tell when the agent job has completed which is no good to me as I need to run some other steps after the job has completed.
Yep, I was expecting that. You could work around that using semaphores but the above approach is much easier and cleaner.
Good luck!
February 24, 2010 at 8:34 am
Sure, but you can even leave the copying of the config file out of the parent package and do it in you sql agent jobs (no need to pass variables etc.). Have done it before and works perfectly.
Do you mean if I have no other variables I need to pass to the parent package aside from those used in the child and thus no need for a parent configuration file? Curious how you do that from within the sql agent job? Think I'm going to need a configuration file for the parent anyway as I need to do other processing.
Yep, I was expecting that. You could work around that using semaphores but the above approach is much easier and cleaner.
Good luck!
Semaphores? Haven't used them before. Is that something to do with locking tables etc?
February 24, 2010 at 9:02 am
Do you mean if I have no other variables I need to pass to the parent package aside from those used in the child and thus no need for a parent configuration file? Curious how you do that from within the sql agent job? Think I'm going to need a configuration file for the parent anyway as I need to do other processing.
If there is nothing to configure in the parent package, then you don't need a configuration file.
In sql agent jobs, you can have multiple steps. One of these steps can be copying a file. Since it is your intention to create two jobs anyway, in one job you copy the "a" file and in the other job, you copy the "b" file. As simple as that.
Semaphores? Haven't used them before. Is that something to do with locking tables etc?
A bit simplified, a semaphore is like a traffic light. You put it on red at the beginning and put it back to green when you're finished. In your package, you make the next task simply wait until the light turns green before it can start. This is often done with an (empty) file: create a file when the first task starts and delete it after it's finished. The second task is not allowed to start before the file has disappeared.
February 24, 2010 at 9:24 am
If there is nothing to configure in the parent package, then you don't need a configuration file.
In sql agent jobs, you can have multiple steps. One of these steps can be copying a file. Since it is your intention to create two jobs anyway, in one job you copy the "a" file and in the other job, you copy the "b" file. As simple as that.
Didn't know you could do that from within an agent job! Is that a specific step type such as Operating System (Cmd Exec)?
A bit simplified, a semaphore is like a traffic light. You put it on red at the beginning and put it back to green when you're finished. In your package, you make the next task simply wait until the light turns green before it can start. This is often done with an (empty) file: create a file when the first task starts and delete it after it's finished. The second task is not allowed to start before the file has disappeared.
I see. So I'd have to change the child slightly to implement the semaphore mechanism (traffic light on at start of package and off at end). Useful to know for other issues I guess.
Thanks for your assistance with all this. Been invaluable. Will let you know how I get on anyway.
As a matter of interest, do you think as a rule I should avoid using parent package variable configurations on packages that already exist and only use them if I'm developing a parent and child setup from scratch? What we're doing here seems quite revolutionary!
February 25, 2010 at 12:58 am
Didn't know you could do that from within an agent job! Is that a specific step type such as Operating System (Cmd Exec)?
Yep, that's the one. In the command box you can just type copy file1 file2 (with the correct drive letter and path of course) or call a bat or cmd file.
Thanks for your assistance with all this. Been invaluable. Will let you know how I get on anyway.
You're welcome and thank you for the compliment.
As a matter of interest, do you think as a rule I should avoid using parent package variable configurations on packages that already exist and only use them if I'm developing a parent and child setup from scratch? What we're doing here seems quite revolutionary!
I use parent / child packages (I call the parent packages "master" packages) all the time (it is a best practice) but I rarely use parent package variables. Actually, the only time I use parent package variables is for auditing so I can track which execution of a package called another. All other configurations are done from the database (which I find more convenient than the xml files). Also, all of my child packages can run on their own.
February 25, 2010 at 8:16 am
Rats! I thought I'd do the dtsconfig overwrite in the parent package as I need it continue and do more processing immediately after it had finished, I'd have to split the package in two otherwise if I was to use the sql agent job Operating System (Cmd Exec) step type. Problem is I'm getting:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\\XXXXX\ProgramFiles\Data Mirror 414\MainDataMirror414.dtsConfig' is denied.".
where XXXXX is my servername and ProgramFiles is a folder share.
I don't think there's any problem with the path as I can see the folder from the Run command.
Could it be that MainDataMirror414.dtsConfig is locked because I'm calling the package later on in the package after the file overwrite is done, so SSIS has already established a connection to that file?
February 26, 2010 at 1:16 pm
March 1, 2010 at 10:12 am
Stupid one really! Worked it out! It was permission issue on a windows share I was using that caused it!
Problem I'm getting now is I run the package from my pc before uploading it to the server. As the child package is running a configuration file after deployment that's looking at a mapped drive on the server, it's looking at E:\....dtsconfig. When I'm running the parent from my pc, it's stating my E: doesn't exist on my pc and then defaults to run from the values from within the package it seems. Thought I'd look to see if I could run the deployment manifest on the server to include a url path e.g. \\servername\folder\....dtsconfig instead of E:\....dtsconfig but I don't get that option. Is there another way of setting this?
Alternative is to always deploy it first and test, but that's going to be a bit onerous really with all the processing I have yet to program to follow the child package's execution.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply