asked
Passing dtsconfig from Master to Child packages
Hello all, I am new to SSIS and have inherited a solution which has roughly 30 packages in a solution. I have lost a week trying to resolve this using the power of Google and ameteurish experimentation. I realise I could spend more time eliminating possibilities but was hoping someone might help me shortcut that a bit. Problem; I am trying to create a master package for a solution containing about 30 packages and over a week have failed to be successful. I want to pass the location of the dtsconfig file from the parent to the child but the child ignores it. From what I have read, I think this should be possible but just can't make it work. Scenario. I have a solution which is housed in C:\Development\SSIS\JPMC POC DEV1. the solution has roughly 30 packages that all use the same dtsconfig file housed in the above path. They all leverage tables in the same DB JPMC_POC_DEV1. To develop changes, I copy the entire solution to a new folder e.g. C:\Development\SSIS\JPMC POC TESTING and create a copy of the DB calling it JPMC_POC_TESTING All 30 packages have the same connections so I edit the new dtsgconfig file in two places to point the Package connections to the new JPMC_POC_TESTING database. Then I edit each of the 30 packages to point it to the newly edited C:\Development\SSIS\JPMC POC TESTING\JPMC POC Configuration.dtsConfig All the packages are currently run individually. When I am done testing I have to repeat the above to put it back in the original folder. ISSUES; What I think I want to do, is create a master package to run all the 30 packages and pass them the location of the dtsconfig file. My intention being that I only have to change the dtsconfig location once in the parent when I am moving between C:\Development\SSIS\JPMC POC DEV1 and C:\Development\SSIS\JPMC POC TESTING. So Currently I have a master package (MASTERPKG) and have given it a string variable called CONFIGPATH at the package scope level. I have entered "C:\Development\SSIS\JPMC POC TESTING\JPMC POC Configuration.dtsConfig" in the variable. In MASTER there is just one "Execute package task" which calls one of the packages called "DIM_HEADCOUNT". In DIM_HEADCOUNT package, I have created a string variable also called CONFIGPATH and set the Package Configurations to use "Parent Package Variable" and use the Parent CONFIGPATH to set the Value property of the DIM_HEADCOUNT CONFIGPATH variable. When I execute the task from MASTER, the child package completes using whatever connections were already defined and does not use the new connections defined in the parent dtsconfig. OBSERVATIONS; In the solution there are 2 datasources ("Target OLEDB JPMC DW" and "Target .Net JPMC DW") and every package in the solution has 2 Connection Managers with the same names. QUESTIONS; What am I doing wrong? Is what I am doing possible? Do the Solution Datasources and Package Connection Managers duplicate each other and/or override each other in some way causing it to appear that the config file is being ignored? Thanks. Dan
It sounds like you are wanting to implement indirect package configuration. Check out this link where this is discussed:- [link text][1] [1]:
http://sqlscape.wordpress.com/tag/direct-vs-indirect-configuration/
Thanks for the link. After reading it I went here
http://msdn.microsoft.com/en-us/library/ms345179.aspx and followed the instructions but it doesn't behave as I expect (maybe my expectations are wrong). I created a variable called ConfigPath in the child package and ConfigPath in the parent. They are both set as string variables. The Scope of the parent variable is at that package level. and the scope of the Child is at the package level. I modify the package configuration of the child to read ConfigPath. Close/Save Child. The Parent variable is populated with C:\Dev\SSIS\Config\TEST.dtsConfig. I set up a connection to the child package and add an EXECUTE PACKAGE task to a Sequence Container in the master. The only things that the dtsConfig sets are connection info to the database (lets call it DEV4). I open the master package and can see that the connections are pointing to DEV4 I execute the container and the child package executes but it's connections are not inherited from the parent (they point to DEV2 database). The Locals window is blank. I stop debug and I check the package configuration and it is pointing to ConfigPath variable, the variable is empty. I expected the child to inherit the ConfigPath variable from the parent, read the config, inherit the DEV4 dataconnection settings and run. Instead, the child totally ignores the parent config file. What am I doing wrong?
Maybe I'm being too verbose. I want to move an SSIS solution to different folders on a server. The folder containing the solution also contains the dtsconfig file. If I move the folder I then have to open 30 packages in the solution and change the config file path. I want to change it once. I can't use environment variable or registry entry. And I can't make the Parent package variable option work as the child packages ignore the parent config variable. Help!! Dan
I think I understand what you are trying to accomplish, and I can understand the confusion on how to make it happen. SSIS has a nasty habit of convoluting things sometimes. I think the confusion is happening when you create your configuration, and choose "Parent Package Variable", and the scoping of that variable. The Parent Package Variable needs to be scoped to the Execute Package container and not the package itself. I just ran a test and proved the point. 1. Create a package variable scoped to the Execute Package Task, in the parent package. 2. Create a Parent Package Variable configuration in each child package that utilizes the parent's task scoped variable's value and assigns it to the child package variable's value. 3. Create expressions that will value the parent package variables with one "master_config" value. Sounds a more difficult than it is, really. It is all in the scope...
Excellent, yes you have understood perfectly and explained it really clearly. The scoping thing had passed me by so I was very excited. Sadly, I still get no joy, so if you can bear it any more, here's my setup; "Master Package" contains just one "Sequence container", the sequence container contains just one "execute package" task. The task executes a package called "Dim Build". Variable setup Child "Dim Build" has empty string variable called "ConfigPathExec" set to EvaluateAsExpression = False, the variable is scoped at the package level and configuration is set to select "ConfigPathExec" from parent. I've quadruple checked spelling and case. Parent "Master Package" has 2 new variables now. "ConfigPathExec" string variable, EvaluateAsExpression=True @[User::ConfigPathMaster], scoped at the Execute package task level. "ConfigPathMaster" string variable containing C:\theconfig\Dev5 config.dtsConfig, scoped at the "Master Package" level. I close all the packages in Visual Studio, right click on the master package and "execute package", it opens, then child package "Dim Build" opens, prompts for password, warns about long text strings, then runs to completion.......AGAINST THE WRONG DATABASE!!!! arggghg The "Master Package" connections in connection manager point to database DEV5 (as per dtsConfig file) but the child just insists on using DEV2 which is where is was previously set, ignoring the passed down config file. So I tried it adding in an intervening ConfigPathSeq variable at the sequence container level and got the same result (i.e. "ConfigPathSeq" string variable, EvaluateAsExpression=true @[User::ConfigPathMaster], scoped to the Sequence container level and set the Exec task variable to evaluate to this.) I've got to be overlooking something really basic ;o( Dan
Sorry Dan, it took a few days to get time to look at this. When you designate your configuration file make sure the only thing you have being configured is the value of the 'ConfigPathMaster' package variable. You should see a string in the XML file that looks similar to the following: C:theconfigDev5config.dtsConfig To verify, you can reload the package to look at the value of both ConfigPathMaster and ConfigPathExec. They should both have the value that you configured in the XML file. You might want to name the child variable something different than the parent's variable name. That can help to eliminate any confusion with variable names/scope. Also, when you execute your package, make sure you have the "Output" window shown. This window will show you informational messages about which configuration the package is attempting to execute from. You should see something similar to the following: Information: 0x40016042 at MasterPackage: The package is attempting to configure from the parent variable "ConfigPathExec".
Hi, the_SQL, thanks for taking the time. I really appreciate it. Sadly, still no joy. I did the above and got the same result :o( The Master package is configured to read a dtsconfig file from C:\Configpath.dtsconfig. That sets the Package scoped ConfigPathMaster variable ok. I can also see that ConfigPathExec has evaluated ok. I gave the child package with an empty string variable scoped at Child package level called ConfigPathChild. The only configuration in the child package is a Parent package variable configuration that takes source from ConfigPathExec and sets ConfigPathChild.Value. Close and save both. Re-open Master and see the info msg "The package is attempting to configure from the XML file "C:ConfigPath.dtsConfig"". I right click on the Execute package task for the Child package. Child opens and there are no configuration messages issued and it fails to set ConfigPathChild to anything. So then I though maybe you meant that every child should have a configuration that reads in C:\Configpath.dtsconfig and sets ConfigPathChild and then a 2nd config that is setup as a parent variable config still using ConfigPathChild as the target variable. So I tried that and while the ConfigPathChild variable gets set, It continues to be ignored with no informational messages. I just can't see what the heck am I not doing to get the child to load the passed config? What do you think? (Desperate) Dan
