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
asked Oct 17, 2011 at 03:03 AM in Default
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.
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
answered Nov 06, 2011 at 08:36 AM